CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

How to calculate sum based on other rows

I have an account transaction table that lists all activities in the account.

id  qty  
1     100    
2     200     
3     -50     
4     20

The id column is an indecation of real time activities as 2 hapend after 1, and 4 happened after 3.

I need to add a column which per each row gives the current total sum:

id  qty    total_sum
1   100    100      / 0 + 100
2   200    300      / 100 + 200
3   -50    250      / 300 - 50
4    20     270      / 250 + 20

This is simple enough. However my table is huge, it contains very old rows which thier data isn't neccecry correct. To avoid it, I would like to calculate the sum from buttom to top, so that at least the newest records will have the currect values. In my case it is doable as for each user I always save the current updated total_sum for today... so I have a value to begin with.

In that case (+ will be consider as - and - as + ) what I get is:

id  qty    total_sum
1   100    0      / 100 - 100
2   200    100 / 300 - 200
3   -50    300      / 250 + 50
4   20     250      / init_value - 20

The problem is that in each row total_sum present the sum before the action on the row was done. for example in row 2 it shows total_sum=100 which is before the action of row 2 was proccessed.

How can I fix it to show the first total_sum column but calculated from buttom?

basicly what i need is:

id  qty    total_sum
1   100    100
2   200    300
3   -50    250
4   20     init_value (which is 270)

This is my code:

SELECT id,qty,(select init_value from x where .....)
    - Sum(a.qty)OVER(ORDER BY id DESC) as total_sum
FROM    a
ORDER  BY id ASC

How can I fix it? (Postgresql 9.3)

Upvotes: 2

Views: 74

Answers (1)

Noel
Noel

Reputation: 10525

You need to use LEAD/LAG function to access the next/previous quantity and use that in SUM.

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

create table myt(
  id_ integer,
  qty_ integer
  );

insert into myt values(1,100);
insert into myt values(2,200);
insert into myt values(3,-50);
insert into myt values(4,20);

Query 1:

select id_, qty_,
lead(qty_) over (order by id_) qty2
from myt
order by id_

Results:

| id_ | qty_ |   qty2 |
|-----|------|--------|
|   1 |  100 |    200 |
|   2 |  200 |    -50 |
|   3 |  -50 |     20 |
|   4 |   20 | (null) |

Query 2:

select id_, qty_,
270 - coalesce((sum(qty2) over (order by id_ desc)),0) total_sum
from (
  select id_, qty_,
  lead(qty_) over (order by id_) qty2
  from myt
) c
order by id_

Results:

| id_ | qty_ | total_sum |
|-----|------|-----------|
|   1 |  100 |       100 |
|   2 |  200 |       300 |
|   3 |  -50 |       250 |
|   4 |   20 |       270 |

Upvotes: 2

Related Questions