CODEWITHSUNDEEP
mysqlsql
The Statistician Magician
The Statistician Magician

Reputation: 214

The count with min

I am examining some housing data and would like a list of the zip codes as well as the count value returned (which all be the same) for the zipcodes that appear the least. However, when I run the following query, I am only getting the answer but not the associated zip codes (from inspection, I know there are multiple zip codes which have the same minimum value). I do realise that this is caused by the GROUP BY function, which is effectively "grouping" the codes by the minimum value, but I'm not sure how get a list of all the zipcodes with the associated minimum value.

SELECT MIN(counted)
FROM (SELECT zipcode, count(*) AS counted
  FROM housingprices
  GROUP BY zipcode)
AS COUNTS

Upvotes: 0

Views: 37

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I think you appears to want :

SELECT zipcode, count(*) AS counted
FROM housingprices
GROUP BY zipcode
HAVING count(*) = (SELECT MIN(counted)
                   FROM (SELECT count(*) AS counted
                         FROM housingprices
                         GROUP BY zipcode)
                  );

Upvotes: 3

Related Questions