Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

WHERE vs. GROUP BY again Expand / Collapse
Author
Message
Posted Sunday, January 27, 2013 2:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, 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.
Post #1412151
Posted Monday, January 28, 2013 12:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
(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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1412218
Posted Monday, January 28, 2013 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, 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?
Post #1412407
Posted Monday, January 28, 2013 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1412420
Posted Monday, January 28, 2013 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 8:40 AM
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.
Post #1412421
Posted Monday, January 28, 2013 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, 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?
Post #1412451
Posted Monday, January 28, 2013 8:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, Visits: 79
Consider the condition Count(something) > 2

Richard, please heed my note about syntactically correct and aggregate functions.
Post #1412455
Posted Monday, January 28, 2013 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 8:40 AM
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.
Post #1412476
Posted Monday, January 28, 2013 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, 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.
Post #1412486
Posted Monday, January 28, 2013 10:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 1,948, Visits: 2,881
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1412550
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse