Hi All,
I am writing a query that UNIONs a bunch of data together. I would like to add
a query to the UNION that will insure that if nothing else is retrieved a zero
will be available.
The data I am retrieving in each part of the UNION looks something like this
item,quantity,ship_date
A, 10, 2006-01-01
A, 5, 2006-02-01
A, 3, 2006-02-01
A, 20, 2006-04-01
Notice the gap in the date sequence.
How can I generate a zero quantity to fill the gap? Either in the original
retrieval or as a seperate query for the UNION or some other way so that when I
GROUP BY the date and SUM the quantity I end up with
item,quantity,ship_date
A, 10, 2006-01-01
A, 8, 2006-02-01
A, 0, 2006-03-01
A, 20, 2006-04-01
The queries whose results are being assembled with the UNION all have the form
SELECT item,
quantity,
DATE_TRUNC('MONTH', source_table.ship_date)::date AS ship_date
FROM source_table;
As always hints, suggestions and URLs for applicable documentation will be
appreciate.
Kind Regards,
Keith