Query with where > claus - 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 2000 Forums

SQL Server Development (2000)

Query with where > claus

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-25 : 14:14:02

I have the following:

SELECT s.babr, isnull(s.SicDescription,'Non-Inst and/or not coded') as sicdescription, SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
FROM SnapsRaw s INNER JOIN
(SELECT babr, SUM(accountmv) ps
FROM snapsraw
WHERE monthend = '12-1-2006'
GROUP BY babr) q ON q.babr = s.babr
WHERE s.MonthEnd = '12-1-2006'
GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
order by sicdescription

I want to be able to add into the where clause something like this, but am unsure how:

WHERE s.MonthEnd = '12-1-2006' and SUM(s.AccountMV) / q.ps > '0.00'

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-25 : 15:00:02

You need to put any expressions that contain aggregates in the HAVING clause instead of the WHERE clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 15:18:31

[code]SELECT s.babr,
isnull(s.SicDescription, 'Non-Inst and/or not coded') as sicdescription,
SUM(s.AccountMV) AS mv,
SUM(s.AccountMV) / q.ps AS Percentile
FROM SnapsRaw as s
INNER JOIN (
SELECT babr,
SUM(accountmv) as ps
FROM snapsraw
WHERE monthend = '12-1-2006'
GROUP BY babr
) as q ON q.babr = s.babr
WHERE s.MonthEnd = '12-1-2006'
GROUP BY s.Babr,
s.SicDescription
having SUM(s.AccountMV) / q.ps > 0.0
order by s.sicdescription[/code]
Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-25 : 16:08:11

i cant thank you two enough

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:11:07

Don't thank us, thank Books Online.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources