Is this possible? I want to pull all dealers that have more than one dealer number
I thought this might work but it didn't.
Code:
select * from Dealer_Profile
where count(dealer_number) > 1
Over the course of years, I am working with an inconsistent database. We have many dealers that have more than one dealer_number, which some of them have only one, which they should. How can I get the desired results? I can provide examples if need be.
you need to use the GROUP BY clause and the HAVING clause. something like this:
Code:
SELECT dealer_name, count(dealer_number) FROM Dealer_Profile
GROUP BY dealer_name
HAVING count(dealer_number) > 1
but if you need all the columns in the table to display. you can try something like this:
Code:
SELECT * FROM Dealer_Profile WHERE dealer_name IN
(SELECT dealer_name FROM Dealer_Profile
GROUP BY dealer_name
HAVING count(dealer_number) > 1)
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by mehere you need to use the GROUP BY clause and the HAVING clause. something like this:
Code:
SELECT dealer_name, count(dealer_number) FROM Dealer_Profile
GROUP BY dealer_name
HAVING count(dealer_number) > 1
but if you need all the columns in the table to display. you can try something like this:
Code:
SELECT * FROM Dealer_Profile WHERE dealer_name IN
(SELECT dealer_name FROM Dealer_Profile
GROUP BY dealer_name
HAVING count(dealer_number) > 1)
|
first option seems to work, thanks!
Was this answer helpful ?
Yes No
Can I take it one step further? Could that query identify dealers that have different dealer numbers? The way you helped identify is it shows all dealers that have more than one dealer number, which is ok.
Was this answer helpful ?
Yes No
not as such ... you'd either have to create a stored procedure and use a temp table to identify them ... or show dealer name and dealer number by using the second query i posted and try to identify them manually.
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by chapman10s Can I take it one step further? Could that query identify dealers that have different dealer numbers? The way you helped identify is it shows all dealers that have more than one dealer number, which is ok. |
Hmm... mehere is right. You'd have to identify what makes a dealer a duplicate if not the id. Then you would have to write a procedure for that, but even then you may get false matches depending on the criteria you use.
Was this answer helpful ?
Yes No
