sql - Rolling sum that resets at a flag - Stack Overflow

I'm trying to sum values in a table and then reset the sum at a boundary.

So, given a table like this

id	value	flag
1	23	1
2	10	1
3	15	0
4	18	0
5	11	0
6	1	1
7	14	1
8	16	1

I'm trying to sum values in a table and then reset the sum at a boundary.

So, given a table like this

id	value	flag
1	23	1
2	10	1
3	15	0
4	18	0
5	11	0
6	1	1
7	14	1
8	16	1

I'd want the sum to reset whenever the flag is 0, so the output from the above would be

sum	calculation
48	(23+10+15)
18	(18)
11	(11)
31	(1+14+16)

The calculation column wouldn't be in the output, it just shows which values should be summed.

Is there a way to do this in SQL (I'm using MySQL). I think what I need to do is turn the table into something like

id	value	group
1	23	1
2	10	1
3	15	1
4	18	2
5	11	3
6	1	4
7	14	4
8	16	4

Then I can sum the groups, but I can't see how to do this either.

• sql
• mysql

Share Improve this question Follow edited yesterday Mark Rotteveel 109k224224 gold badges155155 silver badges217217 bronze badges asked yesterday Kevin JonesKevin Jones 4744 bronze badges 2

• 2 Why to do this in SQL? Use your application, that's far simpler. – Jonas Metzler Commented yesterday
• "Why to do this in SQL?" SQL is expressive enough and it can be achieved in a pretty simple way with windowed functions. – Lukasz Szozda Commented yesterday

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

It is a variation of "gaps-and-islands" and can be very easily solved in SQL by introducing a helper grouping column:

SELECT *, 
  SUM(value) OVER(PARTITION BY grp),
  DENSE_RANK() OVER(ORDER BY grp)
FROM (SELECT *, flag + SUM(1-flag) OVER(ORDER BY id) AS grp FROM tab) AS s
ORDER BY id;

Output:

db<>fiddle demo

---

Assumptions:

• flag values allowed: 0 and 1
• 0 indicates a new group

• In below query I use sum as column name. This is not smart because sum is in the list of reserved words
• (step1) First I created a list of cumulative values for value.
• (step2) I then subtracted this cumulative value from the previous value
• Based on the id, I use GROUP_CONCAT to produce the list for the field caclulation.

SELECT
  `sum`,
  (SELECT GROUP_CONCAT(value) 
   FROM mytable m2 
   WHERE m2.id <= y.id AND m2.id > pid) as calculation
FROM (
  SELECT 
    id,
    cumValue - COALESCE(LAG(cumValue) OVER (order by id),0) as `sum`,
    COALESCE(LAG(id) OVER (ORDER BY id),0) as pid
  FROM (
    SELECT 
      id,
      value,
      flag,
      SUM(value) OVER (ORDER BY id) as cumValue
    FROM mytable
    UNION ALL
    SELECT max(id)+1, 0, 0, SUM(VALUE) FROM mytable
  ) x
  WHERE flag=0
) y

output:

sum	calculation
48	23,10,15
18	18
11	11
31	1,14,16

see: DBFIDDLE

A DBFIDDLE with the steps

Same fiddle, but using CTE's: DBFIDDLE

Some solutions above contain parts of mine.

I regard it as a 'sessionisation'/ 'gaps-and-islands' question: Every row with a value of 0 is the end of a 'session' / an 'island'.

Create a session_id (I personally prefer the term 'sessionisation' to 'gaps-and-islands'), by nesting two queries: one with a counter that is at 1 at the 'session end' and at 0 otherwise, and the other querying this previous fullselect, with a running sum of that counter , naming that running sum the session_id. Then, you have something to group by, finally:

WITH
-- your input ....
indata(id,value,flag) AS (
          SELECT 1,23,1
UNION ALL SELECT 2,10,1
UNION ALL SELECT 3,15,0
UNION ALL SELECT 4,18,0
UNION ALL SELECT 5,11,0
UNION ALL SELECT 6, 1,1 
UNION ALL SELECT 7,14,1
UNION ALL SELECT 8,16,1
)
-- end of input, real query starts here
-- replace following comma with "WITH" ...
,
w_counter AS (
-- sessionisation / gaps and islands solution part 1: 
-- counter = 1 each time flag is 0, else counter = 0
  SELECT
    *   
  , CASE FLAG WHEN 0 THEN 1 ELSE 0 END AS counter
  FROM indata
  ORDER BY id
)
,
w_session AS (
-- sessionisation / gaps-and-islands solution part 2:
-- running sum of "counter" in "w_counter"
  SELECT
    id  
  , value
  , flag
  , SUM(counter) OVER(ORDER BY id) AS session_id
  FROM w_counter
)
SELECT
  session_id
, GROUP_CONCAT(value) AS calculation
, SUM(value)
FROM w_session
GROUP BY 
  session_id;

session_id	calculation	SUM
0	23,10	33
1	15	15
2	18	18
3	11,1,14,16	42