SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WHERE vs. GROUP BY again


WHERE vs. GROUP BY again

Author
Message
Igor Makedon
Igor Makedon
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 79
The following two T-SQL statements return exactly the same row set whatever data is:
SELECT something FROM somewhere WHERE condition GROUP BY grouping
and
SELECT something FROM somewhere GROUP BY grouping HAVING condition
provided that
*) all italic placeholders are replaced with corresponding literally identical strings;
*) both statements are syntactically correct (condition contains no aggregate functions).

I think the above assertion is correct. Am I right please?
Thanks.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27649 Visits: 13268
(condition contains no aggregate functions)


This is very important. Since you specify you can't filter on aggregate values, the HAVING clause acts the same as the WHERE clause. In other words , you have written two identical SELECT statements.

However, if you can filter on aggregate values in the HAVING clause, there are surely not the same.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Igor Makedon
Igor Makedon
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 79
Thank you, Koen. I parenthesized the quoted phrase because I deem it's an implication of "both statements are syntactically correct". Isn't it?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27649 Visits: 13268
Igor Makedon (1/28/2013)
Thank you, Koen. I parenthesized the quoted phrase because I deem it's an implication of "both statements are syntactically correct". Isn't it?


I wouldn't use "correct". If you change the HAVING clause to filter on aggregated values, the statement is still syntactically correct, however it is not the same as the other one.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Richard Cooke
Richard Cooke
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16
The condition in a HAVING clause is different than the condition on a WHERE clause as the HAVING condition acts on the result of the aggregation.

Consider the condition Count(something) > 2

This as a HAVING condition would return a data set when there were more than 2 somethings existed. It is not possible to replicate this condition with the WHERE clause as each individual data row is 'unaware' of other rows with the same value.

to put the answer the other way round.
You can use the WHERE condition in a HAVING condition - but is a misuse of HAVING
You can not always put the HAVING condition in the WHERE condition - If you can, my suggestion is that you do.
Igor Makedon
Igor Makedon
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 79
I wouldn't use "correct".

Me neither, but in the next sentence you are using exactly wording of mine.
And besides, could you please offer your Yes/No answer to my question I put in my original message?
Igor Makedon
Igor Makedon
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 79
Consider the condition Count(something) > 2

Richard, please heed my note about syntactically correct and aggregate functions.
Richard Cooke
Richard Cooke
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16
Excluding the aggregates then yes your assertion is correct and would produce the same results.

But if I were an examiner in a test then I would say that by using the condition in the HAVING clause, that you do not understand the nature of the HAVING clause which is to act on aggregates.

The processes acting within the server would produce the same data set, but would process the original data in different ways. Not sure why you asked the question.
Igor Makedon
Igor Makedon
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 79
I need to optimize some non-SQL code that composes SQL statements dynamically. Some pieces of code look very much alike, but put the a priori non-aggregate condition either in HAVING or in WHERE clauses seemingly at random (different programmers at different times). To incorporate code into a single routine and thus to throw away costs of past development inconsistencies, is my goal.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7995 Visits: 7160
WHERE and HAVING are fundamentally different; they should never be thought of as equivalent or interchangeable. That is, for a given query/subquery, only one of them is right to use.

Use WHERE when you want to filter individual row values.

Use HAVING when you want to filter based on aggregate/group values.

SQL Server used to allow non-aggregate column names in the HAVING clause, and indeed then did not evaluate that condition until after GROUPing had been done; from SQL 2005 on, I don't SQL even allows that any more.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
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