Group by Month/Year and Product with sum of quantity including null records

Hi,
I want to build a query that shows the Total amount of Invoices by month between 2 dates by product, eg:
Year/Month, Product Desc, Amount $, Units 2004-03, Product 1, 1500, 3000
2004-02, Product 1, 0, 0
.
.
.

2002-09, Product 1, 200, 120
.
.

2004-03, Product 2, 1200, 1000

I have a Calendar Table with all the dates for the next 30 years, 2000-2030.
My current query is:

SELECT INV.DESCRIPTION, YY_MM.CL_DATE,
SUM(INV.QUANTITY),SUM(INV.AMOUNT) FROM
(
SELECT
AP.DESCRIPTION ,
I.INVOICE_DATE "INVOICE_DATE",
SUM(ID.QUANTITY) "QUANTITY" ,
SUM(ID.PRICE*ID.QUANTITY) "AMOUNT"
FROM
 APP_PRODUCTS AP,
 INVOICES I,
 INVOICE_DETAILS ID
WHERE
 I.INVOICE_ID = ID.INVOICE_ID
AND I.CUSTOMER_ID=420 -- PARAM
AND ID.APP_PRODUCT_ID=AP.APP_PRODUCT_ID
AND AP.APP_PRODUCT_ID IN ( 2 , 3 ) -- PARAMS GROUP BY AP.DESCRIPTION , I.INVOICE_DATE ORDER BY AP.DESCRIPTION ASC, I.INVOICE_DATE DESC )INV,
(SELECT TO_CHAR( C.CAL_DATE , 'YYYY-MM') AS "CL_DATE"   FROM APP_CALENDAR C
  WHERE C.CAL_DATE > TO_DATE('01/09/2002','DD/MM/yyyy')   AND C.CAL_DATE < SYSDATE
  GROUP BY TO_CHAR( C.CAL_DATE , 'YYYY-MM')   ORDER BY TO_CHAR( C.CAL_DATE , 'YYYY-MM') DESC  )YY_MM
WHERE YY_MM.CL_DATE = TO_CHAR(INV.INVOICE_DATE(+),'YYYY-MM') GROUP BY INV.DESCRIPTION, YY_MM.CL_DATE
ORDER BY INV.DESCRIPTION ASC , YY_MM.CL_DATE DESC; the problems are:
Output is not sorted by date corretly, i have:

2004-03, Product 1, 150, 100
2004-01, Product 1, 100, 70
2004-02, null,null,null
2003-12, null, null, null

seems the order is wrong in case the data is not null. What I am doing wrog?
Thank you. Received on Mon Mar 15 2004 - 21:52:00 CET