Re: group by help please!

On Jan 4, 7:46 am, 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

I suspect that it is the inclusion of "purch.Prod_Code" in the GROUP BY clause that is causing the problem.

This:
(case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end) as "SP-20.2",

May be rewritten as:
SUM(DECODE(PURCH.PROD_CODE,'SP-20.2',PURCH.QTY,0)) AS "SP-20.2" By making similar changes to the other columns that are returned, you would be able to remove "purch.Prod_Code" from the GROUP BY clause.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 04 2008 - 07:30:54 CST