How to do conditional sum with PostgreSQL?

Question

I have the following table:

ID  Quantity     date
100    20     1-NOV-15
100    30     1-OCT-15
100    10     1-OCT-15
100    5      1-AUG-15
101    4      1-AUG-15

I want to sum for each ID all the Quantity till the Date associated with the ID meaning I want to get this:

ID  Quantity     Date      sum
100    20     1-NOV-15      65       // sum all ID 100 till 1-NOV-15
100    30     1-OCT-15      45       // sum all ID 100 till 1-OCT-15
100    10     1-OCT-15      45       // sum all ID 100 till 1-OCT-15
100    5      1-AUG-15       5       // sum all ID 100 till 1-AUG-15
101    4      1-AUG-15       4       // sum all ID 101 till 1-AUG-15

I'm having trouble getting this result. This is what I wrote:

Select ID,Date,SUM(Quantity)
From a
Group by ID,Date
order by ID

I can't find out how to tell the SUM to pass on all records which are the same ID and their Date is smaller.

Gordon Linoff · Accepted Answer · 2015-06-29 20:11:30Z

4

You want a cumulative or running sum.

Select ID, Date, SUM(Quantity) OVER (PARTITION BY ID ORDER BY DATE)
From a;

EDIT:

The default framing option is range between unbounded preceding and current row as opposed to rows between unbounded preceding and current row. If you used the latter, the results would be more like:

ID  Quantity     Date      sum
100    20     1-NOV-15      65       // sum all ID 100 till 1-NOV-15
100    30     1-OCT-15      45       // sum all ID 100 till 1-OCT-15
100    10     1-OCT-15      15       // sum all ID 100 till 1-OCT-15
100    5      1-AUG-15       5       // sum all ID 100 till 1-AUG-15
101    4      1-AUG-15       4       // sum all ID 101 till 1-AUG-15

However, the default is appropriate for this problem.

edited Jun 29, 2015 at 20:11

answered Jun 29, 2015 at 10:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marth Over a year ago

According to the documentation, the default framing option is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. After testing both of your queries, they do return the same (and correct) result.

John Over a year ago

Marth is correct both queries return the same result. Can you please explain how it works? I read the link you gave but I don't understand in the first solution how it knows till where to count? what will you have to change if the desired solution was indeed what he sowed with the 15 and 45 results.

Marth Over a year ago

@John : It's a bit hard explaining the concept of window functions, especially in a comment, but the documentation does a good job I think (although it doesn't say anything about the RANGE clause, but after reading it the first link should be much clearer). As for your second question, it's a bit hard to say really : how do you distinguish between the two 1-OCT-15 rows ? I.e why would the results be 15 -> 45 (ie (5+10) -> (5+10+30)) and not 35 -> 45 ?

Collectives™ on Stack Overflow

How to do conditional sum with PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related