How to filter a group select query? - 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)

How to filter a group select query?

Author

Topic

Fearpig
Starting Member

2 Posts

Posted - 2008-02-18 : 07:05:00

Hello,
Can anyone help me with my query?

Here's my current query:

SELECT TOP (100) PERCENT Area, Pacode, SUM(CurrentMonth) AS Sum_CurrentMonth
FROM dbo.qry_Travis_by_Area_Summary
GROUP BY Pacode, Area
HAVING (Area = N'0010') OR
(Area = N'0011') OR
(Area = N'0019')

...and the output comes out as:

Area Pacode Sum_CurrentMonth
0010 A 7
0011 A 18
0019 A 9
0010 B 6
0011 B 3
0019 B 7

and this is the output I'm aiming for:

Pacode Sum_CurrentMonth
A 34
B 16

Unfortunately I can't just use:

SELECT TOP (100) PERCENT Area, Pacode, SUM(CurrentMonth) AS Sum_CurrentMonth
FROM dbo.qry_Travis_by_Area_Summary
GROUP BY Pacode

Because there are Areas that I need to filter out. Can anyone explain how to use a field to filter with when it is not included in either the "group by" or the "aggregate"?

Cheers.

"Whats wrong with the cat?" - Mrs Schrödinger

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-18 : 07:16:41

select Pacode, SUM(CurrentMonth) AS Sum_CurrentMonth
from dbo.qry_Travis_by_Area_Summary
where Area in ('0010', '0019', '0011')
group by Pacode

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 07:41:39

Learn the difference between HAVING and WHERE.
HAVING is applied AFTER the aggregation, WHERE is applied BEFORE the aggregation.

E 12°55'05.25"
N 56°04'39.16"

Fearpig
Starting Member

2 Posts

Posted - 2008-02-18 : 07:44:24

Thank you both,
I think it was a case of me looking at it for so long I just couldn't see the error!

"Whats wrong with the cat?" - Mrs Schrödinger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources