The Nonsensical Count

  • Comments posted to this topic are about the item The Nonsensical Count

  • Nice, basic question, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Good simple question and back to basics, thanks Steve

    ...

  • Good one! I would clarify the answer as: 

    HAVING ... behaves as a WHERE clause on the aggregated results.

  • If I alter the query to ">2" instead, I get a result of 3, and an error if I change that HAVING to a WHERE. It would appear that BOL is incorrect on this one, and that the HAVING creates an implicit GROUP BY with a single row of output.

    Edit: The author of this post clearly didn't have his brain in gear yet when he wrote this. As pointed out below, aggregate functions are where the aggregation is introduced when there is no GROUP BY.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • srienstr - Tuesday, November 28, 2017 6:54 AM

    If I alter the query to ">2" instead, I get a result of 3, and an error if I change that HAVING to a WHERE. It would appear that BOL is incorrect on this one, and that the HAVING creates an implicit GROUP BY with a single row of output.

    No the having does not create an implicit group by. When there is no group by there are no groups and it treats the entire table as a single group. Just like select count(*) from sys.tables counts the entire collection as a single group.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • srienstr - Tuesday, November 28, 2017 6:54 AM

    If I alter the query to ">2" instead, I get a result of 3, and an error if I change that HAVING to a WHERE. It would appear that BOL is incorrect on this one, and that the HAVING creates an implicit GROUP BY with a single row of output.

    Actually, it's the aggregate function which creates the implicit grouping, if you want to think of it that way. As soon as you put an aggregate function into a statement, SQL Server knows it needs to aggregate, and unless you specify a grouping, it will aggregate over the entire result set.

    Try the query without the HAVING at all, and you'll still get one row with a result of 3.

  • Steve Jones - SSC Editor - Monday, November 27, 2017 9:41 PM

    Comments posted to this topic are about the item The Nonsensical Count

    No, not quite. It behaves as if the whole table is a group, then it applies the HAVING clause to it. It then discards the group(s) that fail the HAVING clause. There is no pretending to be a WHERE clause.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • sknox - Tuesday, November 28, 2017 8:57 AM

    srienstr - Tuesday, November 28, 2017 6:54 AM

    If I alter the query to ">2" instead, I get a result of 3, and an error if I change that HAVING to a WHERE. It would appear that BOL is incorrect on this one, and that the HAVING creates an implicit GROUP BY with a single row of output.

    Actually, it's the aggregate function which creates the implicit grouping, if you want to think of it that way. As soon as you put an aggregate function into a statement, SQL Server knows it needs to aggregate, and unless you specify a grouping, it will aggregate over the entire result set.

    Try the query without the HAVING at all, and you'll still get one row with a result of 3.

    Correct, the HAVING clause doesn't make it an aggregate, nor does the absence of the GROUP BY change the nature of the HAVING clause.

    The documentation is simply incorrect, as one or more folks here have stated. I have submitted a correction to that page via Pull Request (PR) #235. Click on the "Files changed" tab to see the specific changes.

    Take care, Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Well, having learnt SQL through Oracle in the '80s I originally thought this just couldn't be done, so I was surprised when I read BOL. 
    Certainly nonsensical though.

  • And here I thought it was just a simple, basic question.

  • Solomon Rutzky - Tuesday, November 28, 2017 4:20 PM

    The documentation is simply incorrect, as one or more folks here have stated. I have submitted a correction to that page via Pull Request (PR) #235. Click on the "Files changed" tab to see the specific changes.

    The correction has gone live, so the documentation is now correct 🙂 .

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 12 posts - 1 through 11 (of 11 total)

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