# How to SUM previous sum, e.g N = (N-1) + (N-2) + … + 1?

I have a table name "TABLE_A (id integer, no integer)" .

I want to sum "no" with group by "id" and current "sum of no" = previous "sum of no"

Here is my code :

1/ Create table & insert data:

create table table_a (id int, no int);

insert into table_a values(1, 10);
insert into table_a values(1, 20);
insert into table_a values(1, 30);
insert into table_a values(2, 100);
insert into table_a values(2, 200);
insert into table_a values(2, 300);
insert into table_a values(3, 1);
insert into table_a values(3, 2);
insert into table_a values(3, 3);
insert into table_a values(3, 3);

2/ Desired result:

id | sum_of_no
--------------
1  | 60
2  | 660
3  | 669

3/ My solutions (ok):

with t_report_code_temp as
(
select id, sum(no) as t_code
from table_a
group by id
)
select a.id, sum(b.t_code)
from t_report_code_temp a
join t_report_code_temp b on b.id <= a.id
group by a.id
order by 1

My question:

Could you give me the better way to solve ?

-

Improving (?) on Craig Ringer's answer. Less code but not sure if it is more readable or more confusing:

SELECT
id,
SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id
ORDER BY id ;

Tested at SQL-Fiddle

You are right in your comment, when a window function (or an aggregate with OVER()) has an ORDER BY, then the default window is: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which produces a running total with SUM().

The logical flow of execution is:

FROM table_a            -- get all rows of table_a

-- WHERE                -- void here

GROUP BY id             -- make groups of rows, one for each value of "id"
-- SUM(no) AS y   -- and calculate aggregates, like SUM(no)

-- HAVING               -- void here

SELECT                  -- calculate window functions and window aggregates
id,                   -- and any other function used in SELECT or ORDER BY
SUM(y) OVER (ORDER BY id ASC) AS sum_of_no

ORDER BY id             -- order the result set
-
@y-cube: it is better . I will post my comparation . – Luan Huynh Mar 21 '14 at 8:26
as my understand, I think two query are the same . But in real, it not. – Luan Huynh Mar 21 '14 at 8:32
What do you mean? They should produce the same results. Speed/efficiency is another matter. I would guess that it's the same but your tests seem to show otherwise :) – ypercubeᵀᴹ Mar 21 '14 at 8:35
The flow is same in my query as well. GROUP BY is done first and aggregates are calculated, and then the window functions. – ypercubeᵀᴹ Mar 21 '14 at 9:01
first, "group by id" (X) of sum(no) (Y) and next it will sum(Y) follow "group by id of (X)". Thanks ! – Luan Huynh Mar 21 '14 at 9:09

This looks like a candidate for window functions.

First calculate the per-id sums, then do a running sum ordered by ID to get the desired final result.

with t_report_code_temp(id, t_code) as
(
select id, sum(no)
from table_a
group by id
)
SELECT
id,
sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;
-
@Cr-Ringer: I inserted 10.000 rows to test. With my query, it take 40s and your query it take 0.1s. Wow, it is a big difference. Thanks you ! In this link "postgresql.org/docs/9.1/static/tutorial-window.html";, i find "By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause " . – Luan Huynh Mar 21 '14 at 7:12

From Craig Ringer, ypercube.

Here my testing:

create table table_a (id int, no int);

insert into table_a  (1)
select a, a
from generate_series(1, 1000000) a

Craig Ringer's query

with t_report_code_temp(id, t_code) as
(
select id, sum(no)
from table_a
group by id
)
SELECT
id,
sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;

1.000.000 rows -> 5.5s
2.000.000 rows -> 7s   (run (1) twice)

ypercube's query

SELECT
id,
SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id ;

1.000.000 rows -> 3.7s
2.000.000 rows -> 5.5s   (run (1) twice)

I see the magic of window function. Thanks !

-