Stuck on filtering a Group Select Query?

[Fearpig]

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.

[Fearpig]

Please ignore me!!

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

 

"HAVING is applied AFTER the aggregation, WHERE is applied BEFORE the aggregation."

 

 

Correct Solution:

 

SELECT TOP (100) PERCENT Area, Pacode, SUM(CurrentMonth) AS Sum_CurrentMonth

FROM dbo.qry_Travis_by_Area_Summary

WHERE (Area = N'0010') OR

(Area = N'0011') OR

(Area = N'0019')

GROUP BY Pacode