Add cumulative total sum over many columns in Postgres

Question

My table is like this:

+----+--------+--------+--------+---------+
| id | type   | c1     | c2     | c3      |
+----+--------+--------+--------+---------+
| a  |      0 | 10     |     10 | 10      |
| a  |      0 | 0      |     10 |         |
| a  |      0 | 50     |     10 |         |
| c  |      0 |        |     10 | 20      |
| c  |      0 |        |     10 |         |
+----+--------+--------+--------+---------+

I need to the output like this:

+----+---------+--------+--------+---------+
| id | type    | c1     | c2     | c3      |
+----+---------+--------+--------+---------+
| a  |       0 | 10     |     10 | 10      |
| a  |       0 | 0      |     10 |         |
| a  |       0 | 50     |     10 |         |
| c  |       0 |        |     10 | 20      |
| c  |       0 |        |     10 |         |
+----+---------+--------+--------+---------+
|total |     0 | 60     |     50 |  30     |
+------------------------------------------+
|cumulative| 0 | 60     |   110  | 140     |
+------------------------------------------+

My query so far:

WITH res_1 AS 
  (SELECT id,c1,c3,c3 FROM cloud10k.dash_reportcard),
  res_2 AS 
  (SELECT 'TOTAL'::VARCHAR, SUM(c1),SUM(c2),SUM(c3) FROM cloud10k.dash_reportcard)       
SELECT * FROM res_1
UNION ALL 
SELECT * FROM res_2;

It produces a sum total per column.
How can I add the cumulative total sum?

Note: the demo has 3 data columns, my actual table has more than 250.

Answer 1

It would be very tedious and increasingly inefficient to list 250 columns over and over for the sum of columns - an O(n²) problem in disguise. Effectively, you want the equivalent of a window-function to calculate the running total over columns instead of rows.

You can:

1. Transform the row to a set ("unpivot").
2. Run the window aggregate function sum() OVER (...).
3. Transform the set back to a row ("pivot").

WITH total AS (
  SELECT 'total'::text AS id, 0 AS type
       , sum(c1) AS s1, sum(c2) AS s2, sum(c3) AS s3  -- more ...
  FROM   cloud10k.dash_reportcard
  )
TABLE cloud10k.dash_reportcard

UNION ALL  
TABLE total

UNION ALL
SELECT 'cumulative', 0, a[1], a[2], a[3]  -- more ...
FROM  (
   SELECT ARRAY(
      SELECT sum(v.s) OVER (ORDER BY rn)
      FROM   total
           , LATERAL (VALUES (1, s1), (2, s2), (3, s3)) v(rn, s)  -- more ...
      )::int[] AS a
   ) sub;

See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
• SELECT DISTINCT on multiple columns

The last step could also be done with crosstab() from the tablefunc module, but for this simple case it's simpler to just aggregate into an array and break out elements to a separate columns in the outer SELECT.

Alternative for Postgres 9.1

Same as above, but:

...
UNION ALL
SELECT 'cumulative'::text, 0, a[1], a[2], a[3]  -- more ...
FROM  (
   SELECT ARRAY(
      SELECT sum(v.s) OVER (ORDER BY rn)
      FROM  (
         SELECT row_number() OVER (), s
         FROM   unnest((SELECT ARRAY[s1, s2, s3] FROM total)) s  -- more ...
         ) v(rn, s)
      )::int[] AS a
   ) sub;

Consider:

• PostgreSQL unnest() with element number

db<>fiddle here - demonstrating both
_{Old sqlfiddle}

Answer 2

Just add another CTE to get cumulative row:

WITH res_1 AS 
  (SELECT id,c1,c2,c3 
   FROM dash_reportcard),
  res_2 AS 
  (SELECT 'TOTAL'::VARCHAR, SUM(c1) AS sumC1,
          SUM(c2) AS sumC2, SUM(c3) AS sumC3 
   FROM dash_reportcard),
   res_3 AS
   (SELECT 'CUMULATIVE'::VARCHAR, sumC1,
           sumC2+sumC1, sumC1+sumC2+sumC3 
    FROM res_2)
SELECT * FROM res_1
UNION ALL 
SELECT * FROM res_2
UNION ALL 
SELECT * FROM res_3;

Demo here

Answer 3

WITH total AS (
  SELECT 'TOTAL'::VARCHAR, SUM(c1) AS sumc1, SUM(c2) AS sumc2, SUM(c3) AS sumc3
  FROM cloud10k.dash_reportcard
), cum_total AS (
  SELECT 'CUMULATIVE'::varchar, sumc1, sumc1+sumc2, sumc1+sumc2+sumc3
  FROM total
)
SELECT id, c1, c2, c3 FROM cloud10k.dash_reportcard
UNION ALL 
SELECT * FROM total
UNION ALL
SELECT * FROM cum_total;