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

Are you sure you meant to group by Qty?

Mureinik
– Mureinik

2017-01-16 11:14:04 +00:00
Commented Jan 16, 2017 at 11:14 — Mureinik
– Mureinik, Commented Jan 16, 2017 at 11:14
Possible duplicate of SQL - having VS where

Menno
– Menno

2017-01-16 11:19:01 +00:00
Commented Jan 16, 2017 at 11:19 — Menno
– Menno, Commented Jan 16, 2017 at 11:19

Community · Accepted Answer · 2017-05-23 10:29:21Z

17

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, 2017 at 10:29

CommunityBot

11 silver badge

answered Jan 16, 2017 at 11:12

Menno

12.8k14 gold badges59 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zohar Peled Over a year ago

Didn't see you answered before me. Since our answers are pretty much the same, I've deleted mine and upvoted yours.

a55 Over a year ago

Then we have 2 columns that have same name Qty

Collectives™ on Stack Overflow

SQL group by count where count greater than

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related