Randy Banks Randy Banks - 3 months ago 19
SQL Question

Calculate the sum of a values across multiple columns in a table in Postgres (plpgsql)

I have a table that is formatted:

fcst_month | idx1 | idx2 | idx3 | val1 | val2 | ... | valN


I would like to get the sum of all of the 'val's for each fcst_month. It seems like a good way of doing this would be to transpose my table using the tablefunc crosstab() (https://www.postgresql.org/docs/9.3/static/tablefunc.html) and then passing in the column for my particular fcst_month, but reading the docs and other examples on SO, I'm not really understanding how to use this function to achieve my goal.

Could someone give me an example of crosstab() that would achieve this or a similar task? Or perhaps suggest another alternative for achieving my goal?

Answer

You can unpivot the table using json functions row_to_json() and json_each_text(). Additionaly, use with ordinality to get column numbers. Example:

create table a_table (fcst_month int, val1 int, val2 int, val3 int);
insert into a_table values
(1, 10, 20, 30),
(2, 40, 50, 60);

select fcst_month, ordinality, key, value
from a_table, json_each_text(row_to_json(a_table)) with ordinality;

 fcst_month | ordinality |    key     | value 
------------+------------+------------+-------
          1 |          1 | fcst_month | 1
          1 |          2 | val1       | 10
          1 |          3 | val2       | 20
          1 |          4 | val3       | 30
          2 |          1 | fcst_month | 2
          2 |          2 | val1       | 40
          2 |          3 | val2       | 50
          2 |          4 | val3       | 60
(8 rows)

Now it's easy to aggregate values in columns chosen by its position:

select fcst_month, sum(value::int)
from a_table, json_each_text(row_to_json(a_table)) with ordinality
where ordinality > 1
group by 1
order by 1;

 fcst_month | sum 
------------+-----
          1 |  60
          2 | 150
(2 rows)    

Personally I'd use val1+ val2+ val3... even for 39 columns unless I have to deal with something dynamic, e.g. an unknown number of columns.