An aggregate problem - 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

General SQL Server Forums

New to SQL Server Programming

An aggregate problem

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-11-06 : 15:17:24

I've this query like:
SELECT DISTINCT ...AVG(D_VALUE),...
FROM HEADER_DATA,DETAILS_DATA,GUIDE_INFO
WHERE ......
........................................
AND ROUND(AVG(D_VALUE), G_PREC) > ROUND(G_LEVEL, G_PREC)

It's giving me error like -'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 tried to pseudo name the AVG(D__VALUE) in the SELECT, then using it in the WHERE, but it doesn't recognize it.
What would be a get around?
Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-06 : 18:01:27

Look at GROUP BY and HAVING in BOL.

HAVING is how you filter aggregates. It's similar to a WHERE clause for the aggregation


SELECT 	<some fields>, AVG(D_VALUE),... 
FROM 	HEADER_DATA,
	DETAILS_DATA
	GUIDE_INFO
WHERE ......
HAVING	ROUND(AVG(D_VALUE), G_PREC) > ROUND(G_LEVEL, G_PREC)

Also, might as well start using proper JOIN syntax as well

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 22:25:01

you might as well post the actual Query, sample data in DML format and the expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources