group by help please!
Totti
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
Charles Hooper
> 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.
Totti
one thing more please, the above formula as i edited it the way you
told me, does the just that for let's say :
02 1998 0 0 0
this column and every column that has 0 products purchased does not
show, while i want it to show i want the formula to show months from 1
-> 12, with the year and the num. of purchased products, even if it is
0, is that possible?
Charles Hooper
You need to find some way of including rows that do not exist in your
two tables. To do this, you probably need to perform a left outer
join between a list of all months that should be included, and the
current query result that you are receiving.
For example:
SELECT
*
FROM
(SELECT
ADD_MONTHS(TO_DATE('01/01/1998','MM/DD/YYYY'),ROWNUM-1) MY_DATE,
ROWNUM COUNTER
FROM
DUAL
CONNECT BY
LEVEL<=24) C;
MY_DATE COUNTER
--------- ----------
01-JAN-98 1
01-FEB-98 2
01-MAR-98 3
01-APR-98 4
01-MAY-98 5
01-JUN-98 6
01-JUL-98 7
01-AUG-98 8
01-SEP-98 9
01-OCT-98 10
01-NOV-98 11
01-DEC-98 12
01-JAN-99 13
01-FEB-99 14
01-MAR-99 15
01-APR-99 16
01-MAY-99 17
01-JUN-99 18
01-JUL-99 19
01-AUG-99 20
01-SEP-99 21
01-OCT-99 22
01-NOV-99 23
01-DEC-99 24
If you place your current query in an inline view and join it to the
SQL statement above using a LEFT outer join, you will be able to
include all months within the above range
For axample, assume that you are not performing
TO_CHAR(purinv.Inv_Date,'mm') and TO_CHAR(purinv.Inv_Date,'YYYY') in
your current query, but are instead returing the value of
purinv.Inv_Date, and you alias your current query as MQ (short for my
query). The final query might be formatted like this:
SELECT
TO_CHAR(C.MY_DATE,'YYYY') MONTH,
TO_CHAR(C.MY_DATE,'YYYY') YEAR,
... (other columns of interest here)
FROM
(SELECT
ADD_MONTHS(TO_DATE('01/01/1998','MM/DD/YYYY'),ROWNUM-1) MY_DATE,
ROWNUM COUNTER
FROM
DUAL
CONNECT BY
LEVEL<=24) C,
(... your current query here) MQ
WHERE
C.MY_DATE=MQ.INV_DATE(+);
melvin...@gmail.com
Just use an inline view.