Monthly Sales Report SQL grouping and ordering by month filtering nulls

[quique123]

It seems awfully complicated for such a common case scenario.  A monthly grouped sales analysis of the current year is the most basic sales operation one would expect from an ERP yet this is the problem Im having.  Im using iReport to create my jasper reports from my postgre SQL openERP database.

My table is account_invoice and my fields are amount.total, date_invoice and create_date.  The issue is that create_date is the date in which the invoice was created in openERP but date_invoice is the date in which that invoice was finalized.  From what my technician tells me, the difference is that an invoice may be created on the first of the month but until all backorder items are received or until the cashier has time to actually post it and print it out, there is a delay.  That delay is the difference between create_date and date_invoice.

So I fetch my data like so:

selectsum(account_invoice.amount_total), CASE to_char(date_invoice,'mm') WHEN '01' THEN  'Enero'  WHEN '02' THEN 'Febrero'  WHEN '03' THEN  'Marzo' WHEN '04' THEN  'Abril' WHEN '05' THEN  'Mayo' WHEN '06' THEN  'Junio' WHEN '07' THEN  'Julio' WHEN '08' THEN  'Agosto' WHEN '09' THEN  'Septiembre' WHEN '10' THEN  'Octubre' WHEN '11' THEN  'Noviembre' WHEN '12' THEN 'Diciembre' end [/code]

from account_invoice[/code]

GROUP BY case to_char(date_invoice,'mm') WHEN '01' THEN  'Enero'  WHEN '02' THEN 'Febrero'  WHEN '03' THEN  'Marzo' WHEN '04' THEN  'Abril' WHEN '05' THEN  'Mayo' WHEN '06' THEN  'Junio' WHEN '07' THEN  'Julio' WHEN '08' THEN  'Agosto' WHEN '09' THEN  'Septiembre' WHEN '10' THEN  'Octubre' WHEN '11' THEN  'Noviembre' WHEN '12' THEN 'Diciembre' end[/code]

which seems awfully complicated to begin with, but ok.  Now the issue is that this generates a small table in iReport Data Preview with 6 months of data in our openERP so far plus a 7th category with null as the category

March - xxxx

April - yyyy

May - zzzz

June - aaaa

July - bbbbb

August - ccccc

         - dddddd

Where dddd corresponds to null values which are not able to be plotted.  They are reported in a text based report but the plot is not plottable due to a null category.  But thats just a technical issue.  The fact remains that those null category invoices actually belong to the current month, August.  And I would want to group them as such.  So my question is how do I group those nulls into the current month?  One last thing, this results in a table with the months out of order.  The table results in March then February, then May then August or something like that.  So when its plotted in the bar chart, the months are not in order.  I tried order by and the exact same case statement but it orders the months Alphabetically.

[quique123]

Ended up using COALESCE in case anyone is interested...