Re: group by help please!
Date: Fri, 4 Jan 2008 06:42:50 -0800 (PST)
Message-ID: <5adbd41f-fb46-4990-8983-02c29e92668a@r60g2000hsc.googlegroups.com>
On Jan 4, 8:56 am, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> you were right Mr. Hooper ,
> 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?
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(+);
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Jan 04 2008 - 08:42:50 CST