MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code)

SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) > 10 ORDER BY lastupdated DESC 

Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.

9 Answers

try this;

select gid from `gd` group by gid having count(*) > 10 order by lastupdated desc 

I'm not sure about what you're trying to do... maybe something like

SELECT gid, COUNT(*) AS num FROM gd GROUP BY gid HAVING num > 10 ORDER BY lastupdated DESC 

SELECT COUNT(*) FROM `gd` GROUP BY gid HAVING COUNT(gid) > 10 ORDER BY lastupdated DESC; 

EDIT (if you just want the gids):

SELECT MIN(gid) FROM `gd` GROUP BY gid HAVING COUNT(gid) > 10 ORDER BY lastupdated DESC 

Just academic version without having clause:

select * from ( select gid, count(*) as tmpcount from gd group by gid ) as tmp where tmpcount > 10; 

try

SELECT DISTINCT gid FROM `gd` group by gid having count(*) > 10 ORDER BY max(lastupdated) DESC 

There can't be aggregate functions (Ex. COUNT, MAX, etc.) in A WHERE clause. Hence we use the HAVING clause instead. Therefore the whole query would be similar to this:

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value; 

COUNT(*) can only be used with HAVING and must be used after GROUP BY statement Please find the following example:

SELECT COUNT(*), M_Director.PID FROM Movie INNER JOIN M_Director ON Movie.MID = M_Director.MID GROUP BY M_Director.PID HAVING COUNT(*) > 10 ORDER BY COUNT(*) ASC 

-- searching for weather stations with missing half-hourly records

SELECT stationid FROM weather_data WHERE `Timestamp` LIKE '2011-11-15 %' AND stationid IN (SELECT `ID` FROM `weather_stations`) GROUP BY stationid HAVING COUNT(*) != 48; 

-- variation of yapiskan with a where .. in .. select

i think you can not add count() with where. now see why ....

where is not same as having , having means you are working or dealing with group and same work of count , it is also dealing with the whole group ,

now how count it is working as whole group

create a table and enter some id's and then use:

select count(*) from table_name 

you will find the total values means it is indicating some group ! so where does added with count() ;