WHERE not filtering before GROUP BY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE not filtering before GROUP BY?

Author

Topic

JAG7777777
Starting Member

25 Posts

Posted - 2009-11-02 : 07:44:55

Hi,

I thought that WHERE clauses always filtered records from the intermediate FROM table before any grouping took place, however, I have a query that is not doing this....and I cannot see for looking why? No doubt it has something to do with order of execution and aggregate statements - but this is an area I could do with some enlightenment please from an expert

This is my query:

SELECT Spec_Number, Order_Date, Order_Date_Year,Order_Date_Month, Order_Date_Day,
Order_Date_Month_Desc, Order_Date_Day_Desc,
SUM(Ordered_SQM) AS Sum_Ordered_SQM,
SUM(Order_Margin) AS Sum_Ordered_Margin,
(SUM(Order_Margin)/(SUM(Ordered_SQM)/1000)) AS Sum_Ordered_Margin_Per_1000SQM
FROM MyTable
WHERE
Ordered_SQM IS NOT NULL AND
Ordered_SQM <> 0
GROUP BY Spec_Number, Order_Date, Order_Date_Year, Order_Date_Month, Order_Date_Day,
Order_Date_Month_Desc, Order_Date_Day_Desc

I've removed several lines that aren't important to make it easier to read.
From the above, I would not expect there to be any rows returned in the intermediate table (before the GROUP BY clause is invoked) that have 'Ordered_SQM' = 0 - however, I do? And these are causing divide by zero errors.

I can fix it by using HAVING SUM(Ordered_SQM) <> 0 after the GROUP BY - but this doesn't make sense to me considering my assumption regarding the order of evaluation of the WHERE clause and also, I would have thought, creates further processing time?

Can anyone please explain why my WHERE clause is not doing what I would expect

Thanks

JAG7777777

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-02 : 07:47:07

Because -5 and 5 together makes Zero.
HAVING SUM(Ordered_SQM) <> 0 removed these groups too.
But the two single records making up for this Zero-sum are both non-zero.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-02 : 07:47:50

There is also no need to hav both

Ordered_SQM IS NOT NULL AND
Ordered_SQM <> 0

"Ordered_SQM <> 0" automatically removes null records.

N 56°04'39.26"
E 12°55'05.63"

JAG7777777
Starting Member

25 Posts

Posted - 2009-11-02 : 08:12:50

Thanks Peso...whatever you're being paid it is not enough. Answered in seconds and bang on

I would never have found that due to the number of records involved....but hopefully wont get caught by it again...cheers again.

JAG7777777

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-02 : 08:17:48

Thank your for your feedback, and good luck!

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources