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

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 17 Mar 2004 11:31:06 -0800
Message-ID: <4b5394b2.0403171131.54b7ab0e_at_posting.google.com>


korfut_at_lycos.com (Korfut) wrote in message news:<c86be4a4.0403151251.5c90b4c9_at_posting.google.com>...
> 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
>
[inline views deleted]

> )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.

First, the query you posted is not the one that gave you the sample results shown. (note the description in the select is the first column)

Second, i sorted exactly as in your posted ORDER BY clause. What makes you think this is wrong? What order DID you expect?

Lastly, look up NULLs and sorting in the ORACLE manuals. (you'll find they are sorted last! always!)

HTH,
 ed

--
Know thyself. Know thy tools.  -  me
Received on Wed Mar 17 2004 - 20:31:06 CET

Original text of this message