HAVING without GROUP BY

  • Comments posted to this topic are about the item HAVING without GROUP BY


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nice one.

  • thanks for this wonderful question... 🙂

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

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

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • Toby Harman (8/30/2010)


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

    :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:

    CREATE TABLE #QotD (Col1 int, Col2 int);

    go

    INSERT INTO #QotD (Col1, Col2)

    SELECT 1, 1 UNION ALL

    SELECT 1, 2 UNION ALL

    SELECT 1, 3 UNION ALL

    SELECT 2, 4 UNION ALL

    SELECT 2, 5 UNION ALL

    SELECT 2, 6;

    go

    SELECT COUNT(*)

    FROM #QotD

    WHERE Col2 <> 4

    HAVING MAX(Col2) < 5;

    go

    DROP TABLE #QotD;

    go


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Very good question.. I missed my points but learnt something new today.. 😀

  • 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. }

  • 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.

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

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Thank you for the question and the detailed explaination

  • 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.

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

    Bob

  • 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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply