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.

Erwin Brandstetter · Answer 1 · 2022-03-28T00:03:38.193

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:

Transform the row to a set ("unpivot").
Run the window aggregate function sum() OVER (...).
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:

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}

score 1 · Answer 2 · answered Sep 10 '15 at 08:47

1

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

answered Sep 10 '15 at 08:47

Giorgos Betsos

71,379
9
63
98

Hi Giorgos, Thanking you very much.... Is their any another way? Y because more than 250 columns .... – Madhulandoll Sep 10 '15 at 09:02
@Madhulandoll If you have a table with more than 250 columns, then you should seriously consider redesigning your schema. A table with so many columns is almost guaranteed to violate 1NF. – Giorgos Betsos Sep 10 '15 at 11:23

score 1 · Answer 3 · answered Sep 10 '15 at 08:48

1

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;

answered Sep 10 '15 at 08:48

Patrick

29,357
6
62
90

Hi Patrick, Thanking you very much... Is their any another way? Y because columns or more than 250 ... Thanks for suggestion... – Madhulandoll Sep 10 '15 at 09:00

Add cumulative total sum over many columns in Postgres

3 Answers3

Alternative for Postgres 9.1