SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HAVING without GROUP BY


HAVING without GROUP BY

Author
Message
Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 513
Thank you for the question and the detailed explaination
Bob Razumich
Bob Razumich
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 1216
I don't mind being wrong (heck, if I were right all the time...) but it does mystify me a bit that the HAVING clause empties the result set. I selected 0 because I considered the Having clause to act with the WHERE clause.

So for curiosity's sake, I changed HAVING to AND, ran the command and got the error:

Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

That actually helps me to understand it better (I don't claim to be a super pro like many of you here); if HAVING works as an aggregator, the condition says there is nothing to aggregate, thus the empty result set.

Of course, I could be completely wrong with my analysis, too, but for the moment it makes sense to me.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18485 Visits: 12426
brazumich (8/31/2010)
I don't mind being wrong (heck, if I were right all the time...) but it does mystify me a bit that the HAVING clause empties the result set. I selected 0 because I considered the Having clause to act with the WHERE clause.

So for curiosity's sake, I changed HAVING to AND, ran the command and got the error:

Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

That actually helps me to understand it better (I don't claim to be a super pro like many of you here); if HAVING works as an aggregator, the condition says there is nothing to aggregate, thus the empty result set.

Of course, I could be completely wrong with my analysis, too, but for the moment it makes sense to me.

Hi brazumich,

You are not completely wrong, but not completely right either.

Every query that includes aggregate functions and/or a HAVING clause is considered an aggregated query (and every query that includes neither is considered a non-aggregateed query). In each aggregated query, groups are formed (logically, this happens after evaluation the FROM and WHERE clauses; the actual execution plan might differ though).

How these groups are formed is determined by the GROUP BY clause. If there is no such clause, a single group is formed. That is why commonly used queries such as "SELECT COUNT(*), MIN(SomeColumn) FROM MyTable;" return a single row with the rowcount and the maximum SomeCOlumn value for the entire table.

Omitting the GROUP BY before a HAVING clause is far less common than omitting the GROUP BY when an aggregate function is used, but it does have the same effect. So in this question, after flitering out the one row that does not match the WHERE clause, the remaining 5 rows form a single group. That group is then passed through the HAVING clause - and because the MAX(Col2) is 6, the entire group fails the HAVING clause. Hence the empty result set.

If you change the HAVING clause to HAVING MAX(Col2) > 5, you'll get a single row (because the HAVING without GROUP BY forces SQL Server to form one group; and because this time the group does satsify the condition in the HAVING clause). The content of that row will be the number 5, because there are 5 rows in the group (one has fallen victim to the WHERE clause).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Bob Razumich
Bob Razumich
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 1216
Thank you much for the explanation and for taking the time to write it up.

Bob
WayneS
WayneS
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20986 Visits: 10652
Hugo,

Great question!

It's too bad that this latest post wasn't part of the explanation for the question though - it's beautiful.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

webrunner
webrunner
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7694 Visits: 4000
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.

Thanks again - I've been learning a lot lately with these questions.

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7768 Visits: 2629
Good question.....I wish I was a little more awake when I read it. Maybe next time. Thanks.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18485 Visits: 12426
webrunner (8/31/2010)
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.

Thanks again - I've been learning a lot lately with these questions.

- webrunner

But if I added a GROUP BY clause (e.g. GROUP BY Col1), would you expect two rows (with values 3 and 0), or only one row (with value 3)? Why would HAVING behave differently when there is only one group? Or when there are many groups, but none satsifies the condition?

(I do agree that it's coutnerintuitive, though - heck, I picked the wrong answer myself when I answered the question this morning!)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6519 Visits: 2396
Very nice question and great explanation. I could see this coming in handy while troubleshooting someone else's code where they tried to limit their result set but did not know the rules on how SQL Server would handle the having clause. At first glance I would have expected 3 rows returned.
Trey Staker
Trey Staker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 2788
Hugo Kornelis (8/31/2010)
webrunner (8/31/2010)
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.

Thanks again - I've been learning a lot lately with these questions.

- webrunner

But if I added a GROUP BY clause (e.g. GROUP BY Col1), would you expect two rows (with values 3 and 0), or only one row (with value 3)? Why would HAVING behave differently when there is only one group? Or when there are many groups, but none satsifies the condition?

(I do agree that it's coutnerintuitive, though - heck, I picked the wrong answer myself when I answered the question this morning!)


Thanks Hugo for the question. I got it right but only because I had a debate with a coworker years ago if it was possible to have a having statement without a group by.

I also find it interesting that you come up with questions so difficult that even you get them wrong on occasion. lol

BTW I love your questions, I only get about 1/2 of the right but they always make me think.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search