# Grouping data based on cumulative sum

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using Postgresql.

I have price data per day.

``````CREATE TEMP TABLE Price (id,Day, Price) AS
VALUES
(1, 1, 40),
(2, 1, 20),
(3, 1, 50),
(4, 1, 10),
(5, 1, 20),
(6, 1, 60),
(7, 2, 10),
(8, 2, 40),
(9, 2, 10),
(10,2, 20),
(11,2, 10);
``````

I want to assign numbers (1, 2, 3...) to the price data based on the day and the sum of the prices. Every time when the sum > 60, the sum calculation starts again + every time when a new day is reached, the sum calculation starts again. So for example:

Row 1 [day 1, price 40] = 1. Then for row 2 [day 1, price 20] the price sum is 20 + 40 < 61, therefore row 2 also gets assigned to 1. Then for row 3 [day 1, price 50] the price sum is 20 + 40 + 50 > 60, therefore the counting of the sum has to restart and number 2 is assigned to row 3. The result will look like: Does anyone know if this is achievable and how? I understand how to take the `SUM(Price) OVER (PARTITION BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`, but do not know how to restart the sum counting when the condition is met. Thank you in advance for the help!

• how is your table sorted do you have an id or timestamp? Jul 13 '18 at 21:51
• It's sorted based on a 'timestamp without timezone' column. I have a column named date_dt which saves date in format "y-m-d H:M:S" (an example "2017-12-01 09:54:34") and from this I obtained the Day value.
– Nina
Jul 13 '18 at 21:57
• You should always include that stuff, because it's not there it's hard to create it and all your answers are likely to create something different. Jul 13 '18 at 22:00
• Sorry, thank you for pointing it out! It's the first time I am asking a question online, will remember it for the next time
– Nina
Jul 13 '18 at 22:08
• np, that's what we're here for. I added an ID column because without that it simply won't work, but just change that when you apply it. (or update the question with a ts column if you wish). Jul 13 '18 at 22:14

You want something like this,

``````SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
SELECT
id,
day,
price,
sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
FROM price
) AS t;
``````

Working from the inside out we calculate the cumulative sum with `sum()` in a window function,

``````SELECT
id,
day,
price,
sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
FROM price;

id | day | price | cumsum
----+-----+-------+--------
1 |   1 |    40 |     40
2 |   1 |    20 |     60
3 |   1 |    50 |    110
4 |   1 |    10 |    120
5 |   1 |    20 |    140
6 |   1 |    60 |    200
7 |   2 |    10 |     10
8 |   2 |    40 |     50
9 |   2 |    10 |     60
10 |   2 |    20 |     80
11 |   2 |    10 |     90
(11 rows)
``````

Notice our `cumsum` resets on a new day, but it persists past your `60`. We need to fix that, for that we simply look at the prior row, and divide by 60. We don't want to start a new group, unless the last row exceeds a multiple of 60.

``````SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
SELECT
id,
day,
price,
sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
FROM price
) AS t;
id | day | price | cumsum | grp
----+-----+-------+--------+-----
1 |   1 |    40 |     40 |   0
2 |   1 |    20 |     60 |   0
3 |   1 |    50 |    110 |   1
4 |   1 |    10 |    120 |   1
5 |   1 |    20 |    140 |   2
6 |   1 |    60 |    200 |   2
7 |   2 |    10 |     10 |   0
8 |   2 |    40 |     50 |   0
9 |   2 |    10 |     60 |   0
10 |   2 |    20 |     80 |   1
11 |   2 |    10 |     90 |   1
(11 rows)
``````

And you're done.

• This is amazing! Was not even aware that /60 would be possible, thank you so much for the help and for guiding me through the steps!
– Nina
Jul 13 '18 at 22:27
• How robust is this solution, though? Jul 14 '18 at 8:27
• That answer is wrong, you have rows with `id`=5 and 6 in the same `grp` 2, while their `price`s are 20 and 60 which greater than 60. You can see that the picture in the question expects them to be in different groups, 3 and 4. May 11 '19 at 17:16
• Nice answer, but what if I wanted `cumsum` to reset each time it reached >60 rather than resetting each day? In the above example the prices always nicely add up to 60 so nothing gets carried over each time. This is the question I am trying to answer here: here May 17 '19 at 15:45