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: query miscalculation
    ... I'll try to find which row gives this mistake. ... "John Vinson" wrote: ... > records with non-NULL addition codes. ... > Change that portion of the Where clause to ...
    (microsoft.public.access.queries)
  • Serious OLEDB Bug in DELETE Statements ?
    ... I've just spent a day trying to find a serious mistake in an OLEDB DELETE to an ACCESS Database. ... In spite of a quite detailed WHERE clause, the delete query deleted all records in the underlying table. ...
    (microsoft.public.data.oledb)