sum quantities from different dates 1

[RichyT]

Hi, I am trying to write a query that will return total numbers from a quantity column with certain date parameters. I so far have the following query that returns the totals in september 2002 :


select 'SWEDEN' as 'Market', sm.compgroup as 'manufacturer', hp.hpval as 'HP Description',
sum(rs.quantity) as 'month'

from sweden s, manufacts m, horsepower hp

where s.company = m.compname and s.horsepower = hp.hpid and s.regdate = '200209'

group by hp.hpval, m.compgroup


My problem is that I want another column with the total quantity from the previous year (200109). I would like this column to contain a zero if there are no matching records, and also for the current years column to contain a zero if there is a previous year total but no current year.

I hope I have explained this ok, and that someone will be able to help.

Cheers,

Rich

 

[rt63]

select 'SWEDEN' as 'Market', sm.compgroup as 'manufacturer', hp.hpval as 'HP Description'
, sum(case when s.regdate = '200209' then rs.quantity else 0 end) as 'month_2002'
, sum(case when s.regdate = '200109' then rs.quantity else 0 end) as 'month_2001'
from sweden s, manufacts m, horsepower hp
where s.company = m.compname and s.horsepower = hp.hpid
and (s.regdate = '200209' or s.regdate = '200109')
group by hp.hpval, m.compgroup
RT