CODEWITHSUNDEEP
postgresqlgroupingwindow-functions
Scott Klarenbach
Scott Klarenbach

Reputation: 38721

Postgresql running sum of previous groups?

Given the following data:

sequence | amount
1          100000
1          20000
2          10000
2          10000

I'd like to write a sql query that gives me the sum of the current sequence, plus the sum of the previous sequence. Like so:

sequence | current | previous
1 120000 0
2 20000 120000

I know the solution likely involves windowing functions but I'm not too sure how to implement it without subqueries.

Upvotes: 1

Views: 231

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

SQL Fiddle

select
    seq,
    amount,
    lag(amount::int, 1, 0) over(order by seq) as previous
from (
    select seq, sum(amount) as amount
    from sa
    group by seq
) s
order by seq

Upvotes: 4

Filipe Silva
Filipe Silva

Reputation: 21657

If your sequence is "sequencial" without holes you can simply do:

SELECT t1.sequence,
  SUM(t1.amount),
  (SELECT SUM(t2.amount) from mytable t2 WHERE t2.sequence = t1.sequence - 1)
FROM mytable t1
GROUP BY t1.sequence
ORDER BY t1.sequence

Otherwise, instead of t2.sequence = t1.sequence - 1 you could do:

SELECT t1.sequence,
  SUM(t1.amount),
  (SELECT SUM(t2.amount) 
   from mytable t2 
   WHERE t2.sequence = (SELECT MAX(t3.sequence) 
                        FROM mytable t3 
                        WHERE t3.sequence < t1.sequence))
FROM mytable t1
GROUP BY t1.sequence
ORDER BY t1.sequence;

You can see both approaches in this fiddle

Upvotes: 0

Related Questions