WHERE vs. GROUP BY again

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » WHERE vs. GROUP BY again

14 posts, Page 1 of 2

1 2 »»

WHERE vs. GROUP BY again

Rate Topic

Display Mode

Topic Options

Author

Message

Igor Makedon

Posted Sunday, January 27, 2013 2:49 PM

SSC 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

Koen Verbeeck

Posted Monday, January 28, 2013 12:58 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 9,370, Visits: 6,468

(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

Igor Makedon

Posted Monday, January 28, 2013 6:50 AM

SSC 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

Koen Verbeeck

Posted Monday, January 28, 2013 7:12 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 9,370, Visits: 6,468

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

Richard Cooke

Posted Monday, January 28, 2013 7:14 AM

Forum 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

Igor Makedon

Posted Monday, January 28, 2013 8:10 AM

SSC 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

Igor Makedon

Posted Monday, January 28, 2013 8:13 AM

SSC 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

Richard Cooke

Posted Monday, January 28, 2013 8:40 AM

Forum 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

Igor Makedon

Posted Monday, January 28, 2013 9:04 AM

SSC 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

ScottPletcher

Posted Monday, January 28, 2013 10:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 1,320, Visits: 1,771

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)
One man with courage makes a majority. Andrew Jackson

Post #1412550

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions