sql: sum all previous grouped rows

Question

I have the table below:

  date     | shop_id | package_id | quantity
01/01/2017 |   1     |    1       |    1
01/01/2017 |   1     |    2       |    5
01/01/2017 |   2     |    1       |    10
01/01/2017 |   2     |    2       |    4
02/01/2017 |   1     |    1       |    14
02/01/2017 |   1     |    2       |    3
  ....

I want to sum the quantities of all the previous records having the same shop and the same package, like the following

  date     | shop_id | package_id | sum_quantity
01/01/2017 |   1     |    1       |    1
01/01/2017 |   1     |    2       |    5
01/01/2017 |   2     |    1       |    10
01/01/2017 |   2     |    2       |    4
02/01/2017 |   1     |    1       |    15
02/01/2017 |   1     |    2       |    8

  ....

so you want to Keep all the rows, but get the `sum_quantity` with the same `shop_id` and `package_id` added up for the previous days, correct? — RealCheeseLord, Aug 30 '17 at 09:16

score 4 · Answer 1 · answered Aug 30 '17 at 09:15

This is a "running sum" based on shop_id and package_id:

select "date", shop_id, package_id,
       sum(quantity) over (partition by shop_id, package_id order by "date") as sum_quantity
from the_table
order by "date";

sum() is used as a window function (through the over() part). The order by in the window definition makes this a "running" sum, rather then an "overall" sum.

Online example: http://rextester.com/ZOSK13092

sql: sum all previous grouped rows

1 Answers1