Re: group by help please!

On 4 jan, 13:46, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> 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

Just use an inline view. Received on Mon Jan 07 2008 - 02:56:06 CST