Sum By Month And Put Months As Columns

March 11, 2024 Post a Comment

Background I have time series data on a monthly basis and I would like to sum values for each ID, grouped by month and then have the month names as columns rather than as rows. Exa

Solution 1:

You can use an aggregate function with a CASE expression to turn the rows into columns:

select id,
  extra_info,
  sum(casewhenmonth='jan'thenvalueelse0end) jan,
  sum(casewhenmonth='feb'thenvalueelse0end) feb,
  sum(casewhenmonth='mar'thenvalueelse0end) mar,
  sum(casewhenmonth='apr'thenvalueelse0end) apr,
  sum(casewhenmonth='may'thenvalueelse0end) may,
  sum(casewhenmonth='jun'thenvalueelse0end) jun,
  sum(casewhenmonth='jul'thenvalueelse0end) jul,
  sum(casewhenmonth='aug'thenvalueelse0end) aug,
  sum(casewhenmonth='sep'thenvalueelse0end) sep,
  sum(casewhenmonth='oct'thenvalueelse0end) oct,
  sum(casewhenmonth='nov'thenvalueelse0end) nov,
  sum(casewhenmonth='dec'thenvalueelse0end) "dec"
from yt
groupby id, extra_info

See SQL Fiddle with Demo

Solution 2:

tablefunc module

I would use crosstab() for this. Install the additional module tablefunc if you don't have already:

CREATE EXTENSION tablefunc

Basics here: PostgreSQL Crosstab Query

How to deal with extra columns: Pivot on Multiple Columns using Tablefunc

Advanced usage: Dynamic alternative to pivot with CASE and GROUP BY

Setup

CREATE TEMP TABLE tbl
   (id int, extra_info varchar(3), monthdate, valueint);
   
INSERTINTO tbl (id, extra_info, month, value)
VALUES
   (1, 'abc', '2012-01-01', 10),
   (1, 'abc', '2012-02-01', 20),
   (2, 'def', '2012-01-01', 10),
   (2, 'def', '2012-02-01', 5),
   (1, 'abc', '2012-01-01', 15),
   (3, 'ghi', '2012-03-01', 15);

I am using an actual date in the base table, since I am assuming are just hiding that in a effort to simplify your question. But with just month names, there would be nothing to ORDER BY.

Query

SELECT*FROM crosstab(
     $$SELECT id, extra_info, to_char(month, 'mon'), sum(value) ASvalueFROM   tbl
       GROUPBY1,2,monthORDERBY1,2,month$$

    ,$$VALUES
      ('jan'::text), ('feb'), ('mar'), ('apr'), ('may'), ('jun')
    , ('jul'),       ('aug'), ('sep'), ('oct'), ('nov'), ('dec')$$
   )
AS ct (id  int, extra text
   , jan int, feb int, mar int, apr int, may int, jun int
   , jul int, aug int, sep int, oct int, nov int, decint);

Result:

id | extra | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
  1 | abc   |  25 |  20 |     |     |     |     |     |     |     |     |     |
  2 | def   |  10 |   5 |     |     |     |     |     |     |     |     |     |
  3 | ghi   |     |     |  15 |     |     |     |     |     |     |     |     |

Installing the tablefunc module requires some overhead and some learning, but the resulting queries are much faster and shorter and more versatile.

Lab Sql