I have the following brands table with total sales per month as a result of a previous query:
| id | date | total |
|---|---|---|
| 123 | Apr-2012 | 100 |
| 123 | Mar-2012 | 150 |
| 123 | Jan-2012 | 500 |
| 987 | Apr-2012 | 5 |
| 987 | Mar-2012 | 0.10 |
| 987 | Feb-2012 | 8 |
I am looking to achieve the following:
| id | Apr-2012 | Mar-2012 | Feb-2012 | Jan-2012 |
|---|---|---|---|---|
| 123 | 100 | 150 | 0 | 500 |
| 987 | 5 | 0.10 | 8 | 0 |
How do I use the date values as column labels and fill in missing dates with 0 totals?
A crosstab() query for your example would look like this:
To fill in 0 for resulting NULL values (request in comment), use COALESCE():
SELECT brand_id
, COALESCE(jan, 0) AS "Jan-2012"
, COALESCE(feb, 0) AS "Feb-2012"
, COALESCE(mar, 0) AS "Mar-2012"
, COALESCE(apr, 0) AS "Apr-2012"
FROM crosstab(
'SELECT brand_id, month, total
FROM brands
ORDER BY 1'
,$$VALUES ('Jan-2012'::text), ('Feb-2012'), ('Mar-2012'), ('Apr-2012')$$
) AS ct (
brand_id int
, jan numeric -- use actual data type!
, feb numeric
, mar numeric
, apr numeric);
Detailed explanation and links:
Aside: I avoided the reserved word in standard SQL "date" as column name (even if Postgres allows it).
