lundi 29 juin 2015

How to do conditional sum with PostgreSQL?

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.

Aucun commentaire:

Enregistrer un commentaire