Re: group by help please!

On Jan 4, 7:46 am, Totti <saliba.toufic.geo...@xxxxxxxxx> 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.
.

Relevant Pages

  • Re: group by help please!
    ... i want would you please check where the mistake might be? ... from purinv, purch ... Just use an inline view. ...
    (comp.databases.oracle.server)
  • group by help please!
    ... i want would you please check where the mistake might be? ... from purinv, purch ... what shoul be changed in the formula please? ...
    (comp.databases.oracle.server)
  • Re: Ren Ci....Ming Yi..... perform charity works....they are no NKF!
    ... the mistake is not part of the clause yet or the clause of the regulations ... charity works for the benifit of the poor and destitute Singaporeans. ... Ren Ci and Ming Yi are doing. ...
    (soc.culture.singapore)
  • Re: using and scope
    ... it will be destroyed after exiting the using clause" with the statement ... no way I thought that YOU would actually understand you made a mistake. ... bathos is usually a tip-off, even though he disguised it better by ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: using and scope
    ... On 8/9/2011 2:33 PM, Jeff Johnson wrote: ... it will be destroyed after exiting the using clause" with the statement ... I didn't make a mistake. ...
    (microsoft.public.dotnet.languages.csharp)