Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HAVING without GROUP BY


HAVING without GROUP BY

Author
Message
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 2667
Again a great question Hugo! Really makes you think (more than) twice about what to answer..... something new learned...again Smile

_______________________________________________________________________
For better assistance in answering your questions, click here
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3027 Visits: 3745
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!)


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

-------------------
"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
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1807
This is an excellent question, thank you Hugo. It reminded me what my old chess computer says when in teaching mode:

The knight is a tough one. It moves like an L

We are definitely getting spoiled by the high quality brain teasers and perfect explanations from you. Too bad that the back to reality 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 I, not the small l, so the query in question does not return any results...

Oleg
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
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...
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8317 Visits: 11543
webrunner (8/31/2010)
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?

Yes, it is.

Suppose I had used this query:
SELECT COUNT(*)
FROM #QotD
WHERE Col2 <> 4
GROUP BY Col1
HAVING MAX(Col2) < 1;


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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3027 Visits: 3745
Hugo Kornelis (8/31/2010)
webrunner (8/31/2010)
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?

Yes, it is.

Suppose I had used this query:
SELECT COUNT(*)
FROM #QotD
WHERE Col2 <> 4
GROUP BY Col1
HAVING MAX(Col2) < 1;


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.


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

-------------------
"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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21067 Visits: 18258
Excellent question and excellent explanation.

Thanks Hugo



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Chris Cradock
Chris Cradock
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 86
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.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8317 Visits: 11543
Chris Cradock (9/1/2010)
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

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?
SELECT COUNT(*), MAX(Col2)
FROM QotD
WHERE Col2 <> 4
HAVING MAX(Col2) < 5;



Chris Cradock (9/1/2010)
Certainly other DBs complain about it that way.

So be wary, its not documented for a reason.

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:
7.8 <having clause>
(...)
General Rules

1) 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.
(...)


Or, if you prefer a more modern standard, here is a quote from the SQL-2003 standard:
7.10 <having clause>
(...)
Syntax Rules
1) Let HC be the <having clause>. Let TE be the <table expression> that immediately contains HC. If TE
does not immediately contain a <group by clause>, then “GROUP BY ()” is implicit.
(...)

I'll leave it to you to look up the exact definition of GROUP BY () in SQL-2003.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Chris Cradock
Chris Cradock
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 86
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.
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