SQL GroupBy and Having

Murray Brown
Murray Brown used Ask the Experts™
on
Hi. I wanted to ask a question relating to

"The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions"

I'm not sure why this is so. Surely just WHERE could have worked?

It may sound like a stupid question, but I don't see why the SQL construct has to have this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Where filters the rows that are getting selected to get used in the group by
Having filters on the result of the group by
ID Val1 Active
1    A        Y
2    A        N
3    A       Y
4    B        Y
5    B        N

Select val1, count(*)
from tab1
where active = 'Y'     -- leaves rows 1, 3 and 4
group   by val1
having count(*) > 1 -- leaves only A while the count(*) from records with B = 1,
WHERE operates on an individual row, whereas HAVING applies to the group of rows

WHERE is applied before the grouping is made, so if active <> 'Y', the record is not going into the grouping
HAVING is applied after the grouping has been performed, and is applied to the results of the grouping

you could alternatively do

SELECT T.* FROM
(
Select val1, count(*) AS TheCount
from tab1
where active = 'Y'     -- leaves rows 1, 3 and 4
group   by val1
) T
WHERE T.TheCount > 1


In this case the second WHERE is applied to the inner derived query after the grouping has taken place
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial