CODEWITHSUNDEEP
sqloracle-databaseoracle11gsqlplus
Anon4567
Anon4567

Reputation: 15

How do I eliminate duplicate city names while adding their total count in a SQL query?

SELECT city, COUNT(pNo) Total
FROM Zip z JOIN Property p ON (z.zipcode = p.zipcode)
WHERE state = 'AL' AND rent <= 500
GROUP BY city, p.zipcode HAVING COUNT(pNo) >= 15
ORDER BY Total DESC, city;

Above is my code. My goal is to not have multiple listings of the same city, but instead have each city display once and if the city has duplicates, add their totals together. I have tried the DISTINCT clause, but it only eliminates the duplicates without doing doing any adding. I have tried sticking SUM in the code, too, but I can't quite put my finger on where it should go. Any suggestions?

Upvotes: 0

Views: 229

Answers (1)

sgeddes
sgeddes

Reputation: 62831

The problem is you're grouping by zip code, thus creating duplicate city entries (presumably with different counts).

If you want just distinct cities, remove p.zipcode from your GROUP BY and you should be good to go.

Good luck.

Upvotes: 4

Related Questions