Re: group by help please!

From: Charles Hooper <>
Date: Fri, 4 Jan 2008 06:42:50 -0800 (PST)
Message-ID: <>

On Jan 4, 8:56 am, Totti <> 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:
    LEVEL<=24) C;


--------- ----------
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
... (other columns of interest here)
    LEVEL<=24) C,
  (... your current query here) MQ
  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

Original text of this message