Using HAVING - SQL Server Forums

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)

Using HAVING

Author

Topic

LadyReader
Starting Member

18 Posts

Posted - 2009-09-29 : 14:44:38

I have:

Select stdev([Reg Hrs]) as StdDev, Avg([Reg Hrs]) as Mean, WC, Wop, Item
from tblHistory
where [Reg Hrs] <> .1 and [Reg Hrs] <> 0
group by WC, Wop, Item
order by WC, Wop, Item

This works fine, as far as it goes. But I also need to eliminate all rows from the Stdev calculations where [Reg Hrs] is >= +/- 3 std deviations for each distinct group of WC, Wop and Item.

When I try to add that criteria to the Where clause I get an error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I did try putting it in a HAVING clause (never used this before) but:

Column 'CumulativeHistoryFromSyteline.Act Reg Hrs' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Please show me how to structure the query I need or point me in the right direction.

Thanks!

Thank you,
LadyReader

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 15:18:47

Did you try putting "stdev([reg hrs]) >= ..." in the having clause? (rather than just [reg hrs]).
Either that or put any [reg hrs] criteria in the WHERE clause.

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-29 : 15:26:47

GROUP BY stuff
HAVING ABS(stdev([Reg Hrs])) <= 3

Jim

Everyday I learn something that somebody else already knew

LadyReader
Starting Member

18 Posts

Posted - 2009-09-29 : 16:10:32

Thanks all for replies.

Jimf, that worked. Not sure what I did differently since that looks so simple, but thanks anyway!
m

Thank you,
LadyReader

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-29 : 19:05:23

WHERE limits the records you are choosing from
HAVING limits the results obtained in the SELECT FROM WHERE GROUP BY clause

I took the ABSolute value of the already calculated field stdev([reg hrs])

Jim

Everyday I learn something that somebody else already knew

LadyReader
Starting Member

18 Posts

Posted - 2009-09-30 : 08:03:45

Just to be sure I am on the same page as you: I want to eliminate from the std dev CALCULATION any data points whose values are more than 3 std deviations from the mean. Does your query do this, or does it eliminate from the selection RECORD SET the std deviations that are more than 3 std dev from the mean?

Thank you,
LadyReader

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-30 : 08:28:26

So you want to calculate the std dev mean and then remove the outliers from the original data, to then recalulate the std dev and the mean?

Jim

P.S. please include some sample data -- it doesn't have to be huge set, just like 10 records or so so we can be sure we're on the same page.

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources