20

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 columns and be able to fill in missing dates with 0 totals?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jamie
  • 651
  • 2
  • 6
  • 10
  • 2
    I'm not familiar with postgres, but you should probably have a look at the crosstab function: http://www.postgresql.org/docs/9.1/static/tablefunc.html – djfm Apr 05 '14 at 20:26
  • 1
    What you want is a [Pivot(Crosstab for postgres)](http://www.postgresql.org/docs/9.1/static/tablefunc.html) – Antarr Byrd Apr 05 '14 at 20:27
  • 1
    @AntarrByrd damn, you're googling faster than I am :) – djfm Apr 05 '14 at 20:28

1 Answers1

17

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228