SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis / HAVING without GROUP BY / Latest Posts

RE: HAVING without GROUP BY

zymos — Mon, 19 Mar 2012 10:25:45 GMT

Great question and analogy on the HAVING... GROUP BY.

RE: HAVING without GROUP BY

Paul White — Thu, 16 Sep 2010 05:41:39 GMT

Excellent question Hugo - though I too chose the wrong answer.

RE: HAVING without GROUP BY

Artur Komkov — Tue, 07 Sep 2010 12:52:47 GMT

It is real joy to answer such questions. I was able to look at so usual command from another point, turn on common logic and get it right. But I was not sure I was right. Thanks!

RE: HAVING without GROUP BY

L' Eomot Inversé — Tue, 07 Sep 2010 11:24:50 GMT

This was real fun. So was the subsequent discussion. I really ought to get back to looking at QOTD regularly as there seem to be some real gems around now instead of the dire stuff of 6 months ago.I got the right answer by applying a logically wrong method which I've used before (pretend that any aggregates in the having clause but not in the select clause are added to the select clause, that the having clause then operates on the result of the combined select and group by clauses, and that after executing the having clause any spurious additions to the select clause are discarded so that the result has only the required columns). Although this is far from what logically happens it usually delivers the same results (if I've read the standard correcly, that "usually" is actually "always", so that doing it that way - if it were efficient - would be a valid option for an optimizer).

RE: HAVING without GROUP BY

Ameya- Ameyask — Tue, 07 Sep 2010 04:03:40 GMT

Excellent question. I was not aware of this and I gave a wrong answer :-D

RE: HAVING without GROUP BY

Kangana Beri — Wed, 01 Sep 2010 17:15:25 GMT

Great question! Thanks.

RE: HAVING without GROUP BY

Koen Verbeeck — Wed, 01 Sep 2010 08:29:42 GMT

[quote][b]Hugo Kornelis (9/1/2010)[/b][hr][quote][b]da-zero (9/1/2010)[/b][hr]Nice question. But I believe I've seen a similar one here in the QotD (but I may be mistaken).[/quote]Correct. That was April 14, and it was similar but not identical.[url=http://www.sqlservercentral.com/questions/T-SQL/70030/]http://www.sqlservercentral.com/questions/T-SQL/70030/[/url][/quote]I knew it :-)I was just too lazy to look it up. :-)

RE: HAVING without GROUP BY

Hugo Kornelis — Wed, 01 Sep 2010 08:27:56 GMT

[quote][b]da-zero (9/1/2010)[/b][hr]Nice question. But I believe I've seen a similar one here in the QotD (but I may be mistaken).[/quote]Correct. That was April 14, and it was similar but not identical.[url=http://www.sqlservercentral.com/questions/T-SQL/70030/]http://www.sqlservercentral.com/questions/T-SQL/70030/[/url]

RE: HAVING without GROUP BY

Koen Verbeeck — Wed, 01 Sep 2010 08:06:48 GMT

Nice question. But I believe I've seen a similar one here in the QotD (but I may be mistaken).

RE: HAVING without GROUP BY

Hugo Kornelis — Wed, 01 Sep 2010 07:02:56 GMT

[quote][b]Chris Cradock (9/1/2010)[/b][hr]I'm not attempting to say HAVING without GROUP BY is invalid, and yes adding MAX(col2) to the selected columns I would then expect the behaviour you describe (and I would also reason the challenge would have been a "doddle" for everyone as it would then be obvious what HAVING was up to).Its the expectation that HAVING has access to anything that wasn't explicitly worked out at the record selection stage, and thus have access to the MAX(col2) value. SQL server essentially extends the columns selected to resolve the HAVING MAX(col2). So it performs the statement in your response. But that's not in the SQL standard as far as I'm aware.I hope that clarifies my statement.[/quote]Ah, I think I understand. So does this mean that you would have had the same objection if I had included a GROUP BY clause?[code="sql"]SELECT COUNT(*)FROM QotDWHERE Col2 <> 4GROUP BY Col1HAVING MAX(Col2) < 5;[/code]Your misunderstanding is understandable, because we humans are "trained" to read bottom to top (unless you were raised in a culture that writes in a different direction, obviously).SQL should not be interpreted that way. The [i]logical[/i] expression order of the clauses in a query is:1. FROM clause (including all joins), to find (and combine) the table(s) worked on;2. WHERE clause, to throw out individual non-qualifying rows;3. GROUP BY clause, to combine remaining rows to groups;4. HAVING clause, to throw out complete non-qualifying groups;5. SELECT clause, to form the columns in the result set from the columns;6. ORDER BY, to transform the result from an unordered set to an ordered cursor.Because this is the logical order of evaluation, expression in the WHERE, GROUP BY, and HAVING clause can not reference the results of expressions in the SELECT clause, but ORDER BY can (so if you have SELECT Col1 + Col2 AS TheSum, you can use TheSum in the ORDER BY clause but nowhere else).The ANSI standard also says the reverse (that expressions not used in the SELECT clause can not be used in the ORDER BY clause), but SQL Server does allow this. That is a case that you could think of as SQL Server "secretly" adding an extra column to the SELECT list. But again, that is for ORDER BY, not for HAVING.I hope this clarifies the issue. If not, then please don't hesitate to ask further questions!

RE: HAVING without GROUP BY

Chris Cradock — Wed, 01 Sep 2010 05:22:14 GMT

I'm not attempting to say HAVING without GROUP BY is invalid, and yes adding MAX(col2) to the selected columns I would then expect the behaviour you describe (and I would also reason the challenge would have been a "doddle" for everyone as it would then be obvious what HAVING was up to).Its the expectation that HAVING has access to anything that wasn't explicitly worked out at the record selection stage, and thus have access to the MAX(col2) value. SQL server essentially extends the columns selected to resolve the HAVING MAX(col2). So it performs the statement in your response. But that's not in the SQL standard as far as I'm aware.I hope that clarifies my statement.

RE: HAVING without GROUP BY

Hugo Kornelis — Wed, 01 Sep 2010 04:37:20 GMT

[quote][b]Chris Cradock (9/1/2010)[/b][hr]Given that 'having' should be executed after the aggregate has been formed, and code2 isn't in the column list, I don't see why the line is even valid[/quote]Why do you think that Col2 should be in the column list? There is no rule that says that HAVING can only use expressions from the column list. Would you also have objected to the code below (that still produces an empty result set?[code="sql"]SELECT COUNT(*), MAX(Col2)FROM QotDWHERE Col2 <> 4HAVING MAX(Col2) < 5;[/code][quote][b]Chris Cradock (9/1/2010)[/b][hr]Certainly other DBs complain about it that way.So be wary, its not documented for a reason.[/quote]Which DBs did you test this on? I only have access to SQL Server and *cough*MS-Access*cough*, and it behaves the same in both versions. I'd love to hear which ones produce a different result, as that would be a violation of the ANSI/ISO standards for those products.And it is far from "not documented". It is only "not documented well in Books Online". The behaviour of a query with HAVING but no GROUP BY is documented very explicitly in the SQL standards. Here are some relevant quotes from the (agree, very old, but still the basis for many modern RDBMSes) SQL-92 standard:[quote]7.8 <having clause>(...)General Rules1) Let T be the result of the preceding <from clause>, <where clause>, or <group by clause>. If that clause is not a <group by clause>, then T consists of a single group and does not have a grouping column.(...)[/quote]Or, if you prefer a more modern standard, here is a quote from the SQL-2003 standard:[quote]7.10 <having clause>(...)Syntax Rules1) Let HC be the <having clause>. Let TE be the <table expression> that immediately contains HC. If TEdoes not immediately contain a <group by clause>, then “GROUP BY ()” is implicit.(...)[/quote]I'll leave it to you to look up the exact definition of GROUP BY () in SQL-2003.

RE: HAVING without GROUP BY

Chris Cradock — Wed, 01 Sep 2010 03:08:29 GMT

Given that 'having' should be executed after the aggregate has been formed, and code2 isn't in the column list, I don't see why the line is even valid - it should complain about a missing 'col2'. Certainly other DBs complain about it that way.So be wary, its not documented for a reason.

RE: HAVING without GROUP BY

SQLRNNR — Tue, 31 Aug 2010 19:27:32 GMT

Excellent question and excellent explanation.Thanks Hugo

RE: HAVING without GROUP BY

webrunner — Tue, 31 Aug 2010 13:03:07 GMT

[quote][b]Hugo Kornelis (8/31/2010)[/b][hr][quote][b]webrunner (8/31/2010)[/b][hr]I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?[/quote]Yes, it is.Suppose I had used this query:[code="sql"]SELECT COUNT(*)FROM #QotDWHERE Col2 <> 4GROUP BY Col1HAVING MAX(Col2) < 1;[/code]I guess a lot more poeple would have correctly predicted the empty result set. We are all used to metally checking off which groups satisfy the HAVING condition if both GROUP BY and HAVING are in a query. We are also used to expecting a single row result set when aggregates are used without GROUP BY. We are not programmed to do any additional checking on a HAVING without GROUP BY, because this is a very seldom-used construction.That is what I based this question on - those two automatisms for two "normal" constructions, that lead us to incorrect expectations when the ingredients are mixed in an unusual way.[/quote]Great, thanks once more! I am happier to have learned this lesson correctly than I would have been had I guessed correctly and not understood the answer.- webrunner

RE: HAVING without GROUP BY

Hugo Kornelis — Tue, 31 Aug 2010 12:59:43 GMT

[quote][b]webrunner (8/31/2010)[/b][hr]I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?[/quote]Yes, it is.Suppose I had used this query:[code="sql"]SELECT COUNT(*)FROM #QotDWHERE Col2 <> 4GROUP BY Col1HAVING MAX(Col2) < 1;[/code]I guess a lot more poeple would have correctly predicted the empty result set. We are all used to metally checking off which groups satisfy the HAVING condition if both GROUP BY and HAVING are in a query. We are also used to expecting a single row result set when aggregates are used without GROUP BY. We are not programmed to do any additional checking on a HAVING without GROUP BY, because this is a very seldom-used construction.That is what I based this question on - those two automatisms for two "normal" constructions, that lead us to incorrect expectations when the ingredients are mixed in an unusual way.

RE: HAVING without GROUP BY

UMG Developer — Tue, 31 Aug 2010 12:27:31 GMT

Nice question thanks!Now if I would just pay more attention and read carefully. (I read it as greater than...)I should have known better because I think the last question was the > version, so this would have been a complete duplicate...

RE: HAVING without GROUP BY

Oleg Netchaev — Tue, 31 Aug 2010 10:55:23 GMT

This is an excellent question, thank you Hugo. It reminded me what my old chess computer says when in teaching mode:[quote]The knight is a tough one. It moves like an L[/quote]We are definitely getting spoiled by the high quality brain teasers and perfect explanations from you. Too bad that the [i]back to reality [/i]day will inevitably come when someone will post something like:Ha-Ha, got you. That seIect word has a spelling error in it because the third letter there is a capital [b]I[/b], not the small [b]l[/b], so the query in question does not return any results...Oleg

RE: HAVING without GROUP BY

webrunner — Tue, 31 Aug 2010 09:19:25 GMT

[quote][b]Hugo Kornelis (8/31/2010)[/b][hr][quote][b]webrunner (8/31/2010)[/b][hr]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[/quote]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!)[/quote]Sorry, I think my first response was ambiguous. Rather than thinking the result could have many rows, I meant I didn't expect that SELECT COUNT(*) without a GROUP BY could return 0 rows - I figured it would return 1 row (populated with a total of 0 if the HAVING condition was not satisfied). I think I answered "One row displaying the value 0" as a result, but I'm not sure if there's a way to go back and check what I answered.I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?Thanks again,webrunner

RE: HAVING without GROUP BY

Richard M. — Tue, 31 Aug 2010 09:04:03 GMT

Again a great question Hugo! Really makes you think (more than) twice about what to answer..... something new learned...again :)

RE: HAVING without GROUP BY

Trey Staker — Tue, 31 Aug 2010 08:47:49 GMT

[quote][b]Hugo Kornelis (8/31/2010)[/b][hr][quote][b]webrunner (8/31/2010)[/b][hr]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[/quote]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!)[/quote]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.

RE: HAVING without GROUP BY

KWymore — Tue, 31 Aug 2010 08:35:25 GMT

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.

RE: HAVING without GROUP BY

Hugo Kornelis — Tue, 31 Aug 2010 08:26:46 GMT

[quote][b]webrunner (8/31/2010)[/b][hr]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[/quote]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!)

RE: HAVING without GROUP BY

Daniel Bowlin — Tue, 31 Aug 2010 07:43:25 GMT

Good question.....I wish I was a little more awake when I read it. Maybe next time. Thanks.

RE: HAVING without GROUP BY

webrunner — Tue, 31 Aug 2010 07:34:20 GMT

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

RE: HAVING without GROUP BY

WayneS — Tue, 31 Aug 2010 07:14:08 GMT

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

RE: HAVING without GROUP BY

Bob Razumich — Tue, 31 Aug 2010 07:11:56 GMT

Thank you much for the explanation and for taking the time to write it up.Bob

RE: HAVING without GROUP BY

Hugo Kornelis — Tue, 31 Aug 2010 07:01:05 GMT

[quote][b]brazumich (8/31/2010)[/b][hr]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 1An 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.[/quote]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).

RE: HAVING without GROUP BY

Bob Razumich — Tue, 31 Aug 2010 06:33:04 GMT

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 1An 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.

RE: HAVING without GROUP BY

mbova407 — Tue, 31 Aug 2010 06:14:11 GMT

Thank you for the question and the detailed explaination

RE: HAVING without GROUP BY

ronmoses — Tue, 31 Aug 2010 05:57:22 GMT

Great question. I was torn between the correct answer and the single record returning 0... I don't mind having gotten it wrong. :-Dron

RE: HAVING without GROUP BY

paul.knibbs — Tue, 31 Aug 2010 03:27:51 GMT

I was expecting an answer of 3 or 5, but I wasn't sure which, so I created a temporary table to check--was somewhat surprised to see no rows at all returned, but now Hugo has explained the answer I see the logic.

RE: HAVING without GROUP BY

Open Minded — Tue, 31 Aug 2010 03:24:09 GMT

Another good one. I have been losing points lately but I don't miss them.{Sorry, I meant I was losing, not getting, points lately but its alright. Now I hope to remember if I get an SQL gotcha in work for possible QOTD. }

RE: HAVING without GROUP BY

tushkieeeee — Tue, 31 Aug 2010 02:15:01 GMT

Very good question.. I missed my points but learnt something new today.. :-D

RE: HAVING without GROUP BY

Hugo Kornelis — Tue, 31 Aug 2010 01:13:49 GMT

[quote][b]Toby Harman (8/30/2010)[/b][hr]Nice. Helps if I remember that 6 > 5 :blush:[/quote]:laugh: I feel your pain. I managed to miss the point as well - on my own question! :w00t:Thanks everyone for the nice words!For those who want to see for themselves, or try playing around with the issue, here is some repro code:[code="sql"]CREATE TABLE #QotD (Col1 int, Col2 int);goINSERT INTO #QotD (Col1, Col2)SELECT 1, 1 UNION ALLSELECT 1, 2 UNION ALLSELECT 1, 3 UNION ALLSELECT 2, 4 UNION ALLSELECT 2, 5 UNION ALLSELECT 2, 6;goSELECT COUNT(*)FROM #QotDWHERE Col2 <> 4HAVING MAX(Col2) < 5;goDROP TABLE #QotD;go[/code]

RE: HAVING without GROUP BY

Saurabh Dwivedy — Mon, 30 Aug 2010 23:59:31 GMT

Thanks Hugo - this is an awesome question! Tests fundamentals and has appropriate choices as well... Pls keep posting such gems!

RE: HAVING without GROUP BY

Toby Harman — Mon, 30 Aug 2010 22:32:16 GMT

Nice. Helps if I remember that 6 > 5 :blush:

RE: HAVING without GROUP BY

ziangij — Mon, 30 Aug 2010 21:43:44 GMT

thanks for this wonderful question... :-)

RE: HAVING without GROUP BY

b0b555 — Mon, 30 Aug 2010 21:25:26 GMT

Nice one.

HAVING without GROUP BY

Hugo Kornelis — Mon, 30 Aug 2010 20:37:45 GMT

Comments posted to this topic are about the item [B]HAVING without GROUP BY[/B]