November 27, 2017 at 9:41 pm
Comments posted to this topic are about the item The Nonsensical Count
November 27, 2017 at 10:29 pm
Nice, basic question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 28, 2017 at 1:52 am
Good simple question and back to basics, thanks Steve
...
November 28, 2017 at 6:09 am
Good one! I would clarify the answer as:
HAVING ... behaves as a WHERE clause on the aggregated results.
November 28, 2017 at 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.
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.
November 28, 2017 at 8:55 am
srienstr - Tuesday, November 28, 2017 6:54 AMIf 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/
November 28, 2017 at 8:57 am
srienstr - Tuesday, November 28, 2017 6:54 AMIf 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.
November 28, 2017 at 10:35 am
Steve Jones - SSC Editor - Monday, November 27, 2017 9:41 PMComments 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.
November 28, 2017 at 4:20 pm
sknox - Tuesday, November 28, 2017 8:57 AMsrienstr - Tuesday, November 28, 2017 6:54 AMIf 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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
November 29, 2017 at 1:57 am
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.
November 29, 2017 at 6:31 am
And here I thought it was just a simple, basic question.
December 7, 2017 at 10:00 am
Solomon Rutzky - Tuesday, November 28, 2017 4:20 PMThe 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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • 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