group by help please!

From: Totti <saliba.toufic.george_at_gmail.com>
Date: Fri, 4 Jan 2008 04:46:07 -0800 (PST)
Message-ID: <fe6f81e6-19a8-45e5-b1cb-c57cc62e4c14@t1g2000pra.googlegroups.com>

hi all i have the following formula, it is giving me 70% of the result i want would you please check where the mistake might be?

the formula is :

SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
(case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end)
as "SP-20.2",
(case when purch.Prod_Code = 'SP-20.1' then sum(purch.qty) else 0 end)
as "SP-20.1",
(case when purch.Prod_Code = 'SP-20.6' then sum(purch.qty) else 0end)
as "SP-20.6"
from purinv, purch
where purinv.Code = purch.Inv_Code and purch.Prod_Code in ('SP-20.2', 'SP-20.1', 'SP-20.6')
group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') ,purch.Prod_Code order by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')

---

the result i am getting:

MO YEAR SP-20.2 SP-20.1 SP-20.6 -- ---- ---------- ---------- ----------

01 1998          0        140          0
01 1998        400          0          0
01 1998          0          0         80
01 1999          0          0         89
02 1999          0        163          0
02 1999        456          0          0
02 1999          0          0         86
03 1998          0          0         99
03 1999          0          0         81
04 1998          0        163          0
*********************************************************************************************************

the result i want :
is to gather all 1998 for sp.20.2 lets say in one, so on so forth , what shoul be changed in the formula please?

the result i want is like such:
first everything in 1998, then 1999

Month Year SP-20.2 SP-20.1 SP-20.6 ----- ---- ---------- ---------- ----------

   01 1998        400        140         80
   02 1998          0          0          0
   03 1998          0          0         99
   04 1998          0        163          0
   05 1998        420          0         90
   06 1998          0          0          0
   07 1998          0        155         88
   08 1998          0        151         85
   09 1998        481        145         81
   10 1998          0          0          0
   11 1998          0          0        110
   12 1998          0          0          0
   01 1999          0          0         89
   02 1999        456        163         86
   03 1999          0          0         81
   04 1999          0          0          0

any help appreciated, thank you all Received on Fri Jan 04 2008 - 06:46:07 CST