Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer
    
@y-cube: it is better . I will post my comparation . –  Innnh Mar 21 at 8:26
    
as my understand, I think two query are the same . But in real, it not. –  Innnh Mar 21 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 at 8:35
1  
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 at 9:01
1  
first, "group by id" (X) of sum(no) (Y) and next it will sum(Y) follow "group by id of (X)". Thanks ! –  Innnh Mar 21 at 9:09
show 3 more comments

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;
share|improve this answer
    
@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 " . –  Innnh Mar 21 at 7:12
add comment

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 !

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.