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.

score 342 · Answer 1 · answered Nov 19 '08 at 12:52

342

try this;

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

answered Nov 19 '08 at 12:52

Ali Ersöz

15,860
11
50
64

49

+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. – Cruachan Nov 19 '08 at 12:59
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 Oct 14 '11 at 00:27
1

I figured it out.. you can add to the HAVING clause like so: `HAVING variations > 0 OR (stock = 1 OR quantity > 0)` – nnyby Oct 14 '11 at 00:55

score 31 · Answer 2 · answered Nov 19 '08 at 12:52

31

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

answered Nov 19 '08 at 12:52

Greg

316,276
54
369
333

2

MSSQL gives *"invalid column name"* parse error for `num`. +1 anyway for the clean syntax (could be my setup, or ms... ahh well). – samus Jun 13 '17 at 14:04
Provide an alias for all columns in the select. – Adil Khalil Jun 15 '17 at 13:34

Winston Smith · Answer 3 · 2008-11-19T12:58:15.040

24

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

edited Nov 19 '08 at 12:58

answered Nov 19 '08 at 12:52

Winston Smith

21,585
10
60
75

Thanks Joe but that returns the COUNT() - I am looking to return all the gid's which have a COUNT(*) more then 10 – Nov 19 '08 at 12:54
1

There is no need to Min() in there. – Ali Ersöz Nov 19 '08 at 13:02

score 20 · Answer 4 · answered Sep 10 '13 at 12:25

20

Just academic version without having clause:

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

answered Sep 10 '13 at 12:25

Máťa - Stitod.cz

885
7
12

score 18 · Answer 5 · edited Jan 20 '16 at 09:34

18

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;

edited Jan 20 '16 at 09:34

Michele La Ferla

6,775
11
53
79

answered Jan 20 '16 at 09:11

pushkarr

181
1
3

score 15 · Answer 6 · answered Nov 19 '08 at 12:52

15

try

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

answered Nov 19 '08 at 12:52

sme

5,673
7
32
30

score 10 · Answer 7 · answered Sep 05 '19 at 13:45

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

score 6 · Answer 8 · answered Nov 16 '11 at 13:22

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

score 1 · Answer 9 · edited Oct 28 '12 at 12:09

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

score 0 · Answer 10 · edited Mar 23 '23 at 06:35

0

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

edited Mar 23 '23 at 06:35

Suraj Rao

29,388
11
94
103

answered Mar 23 '23 at 06:33

Harshal Mankar

46
4

MySQL - Using COUNT(*) in the WHERE clause

10 Answers10

Linked

Related