SQL group by count where count greater than

Question

I have this SQL that counts the groups, but I want to say where the count is greater than 1, can anyone help as it doesn't currently work?

select Code, Qty, Count(Qty) from Product where ItemName = 'Banana'
and Count(Qty) > 1
Group by Code, Qty order by 3 desc

Possible duplicate of [SQL - having VS where](http://stackoverflow.com/questions/9253244/sql-having-vs-where) — Menno, Jan 16 '17 at 11:19

score 16 · Accepted Answer · edited May 23 '17 at 10:29

16

You should put this condition in the HAVING-clause:

select Code, Qty, Count(Qty) Qty
from Product 
where ItemName = 'Banana'
Group by Code
having count(Qty) > 1
order by 3 desc

HAVING is evaluated after GROUP BY while WHERE is evaluated before, meaning that WHERE-clauses will filter on recordlevel while HAVING-clauses filter on aggregates.

For a more detailed explanation, check SQL - having VS where

I would also recommend you to read Logical Processing Order of the SELECT statement

edited May 23 '17 at 10:29

Community

1
1

answered Jan 16 '17 at 11:12

Menno

12,175
14
56
88

5

Didn't see you answered before me. Since our answers are pretty much the same, I've deleted mine and upvoted yours. – Zohar Peled Jan 16 '17 at 11:15
Then we have 2 columns that have same name `Qty` – a55 Dec 28 '20 at 19:35

SQL group by count where count greater than

1 Answers1