CODEWITHSUNDEEP
mysqlsqloracle-database
m0a
m0a

Reputation: 1005

Count having in a SELECT DISTINCT query?

So I have this query:

SELECT DISTINCT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5) FROM table s
WHERE CITY IN (...)
AND STATE IN (...)

How would I add to this to only get count(s.ID) > 20 so I am only getting distinct city/state/zips that have above 20 IDs in the table?

I assume I need some sort of subquery, but I am unsure of how to accomplish it. Thank you.

Upvotes: 1

Views: 741

Answers (4)

stackFan
stackFan

Reputation: 1608

Use HAVING if you need to filter on aggregates. HAVING is applied after grouping such as below

SELECT DISTINCT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5), count(s.id) as count FROM table s
WHERE CITY IN (...)
AND STATE IN (...) 
GROUP BY s.CITY, s.STATE, SUBSTR(s.ZIP,1,5)
HAVING count > 20;

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Just another way using a subquery

SELECT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5)
FROM Table S JOIN
(
  SELECT ID, COUNT(ID) Cnt
  FROM Table
  GROUP BY ID
) SS
ON S.ID = SS.ID
WHERE CITY IN (...)
AND STATE IN (...)
AND Cnt > 20

Upvotes: 1

Harvey Dobson
Harvey Dobson

Reputation: 86

You could look at the HAVING clause: https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

I'm not exactly sure how it would work with your data. 

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

Obviously you don't need to GROUP BY as you are selecting DISTINCT... So could be something like this?

SELECT DISTINCT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5), COUNT(s.ID) FROM table s HAVING COUNT(s.ID) > 20

Hope this helps!

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Group by the values you want to select and take only those groups having more than 20 records

SELECT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5) 
FROM table s
WHERE CITY IN (...)
AND STATE IN (...)
GROUP BY s.CITY, s.STATE, SUBSTR(s.ZIP,1,5) 
HAVING count(*) > 20

Upvotes: 1

Related Questions