MySQL - Using COUNT(*) in the WHERE clause

Admin 1
Viewed 345545

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.

aggregation having count mysql
9 Answers

try this;

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

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
user_241810 
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
Joe_350860

try

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

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
user_53753990

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() ;

user_23773230
Related