Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Rolling sum that resets at a flag

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.

Tags:

2

Answers


  1. 0
    • 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

    Login or Signup to reply.
  2. 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:

    enter image description here

    db<>fiddle demo

    Assumptions:

    • flag values allowed: 0 and 1
    • 0 indicates a new group
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search