MySQL - Using COUNT(*) in the WHERE clause

Question

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.

Solution 1

try this;

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

Solution 2

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

Solution 3

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

Solution 4

Just academic version without having clause:

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

Solution 5

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;

View more solutions

Comments

[Admin]

Thanks Joe but that returns the COUNT() - I am looking to return all the gid's which have a COUNT(*) more then 10

[Cruachan]

+1 for having This is always the clause that They Don't Bother To Teach Properly on sql courses or books and knowing about it generally the sign that the coder has progressed beyond novice level.

[György Balássy]

There is no need to Min() in there.

[nnyby]

What if you are trying to use the COUNT() as part of a boolean OR expression? e.g. AND ((stock = 1 OR quantity > 0) OR (COUNT(v.id) > 0)

[nnyby]

I figured it out.. you can add to the HAVING clause like so: HAVING variations > 0 OR (stock = 1 OR quantity > 0)

[samus]

MSSQL gives "invalid column name" parse error for num. +1 anyway for the clean syntax (could be my setup, or ms... ahh well).

[Adil Khalil]

Provide an alias for all columns in the select.

[intumwa]

Excellent. I could give it +2 if it was possible.