SQL Query Conditional accumulation

Question

it is possible to display accumulated data, resetting the count based on a condition? I would like to create a script to accumulate if there is value 1 in cell number, but if another value the count should be restarted. Something like what is displayed in the column cumulative_with_condition.

+----+------------+--------+
| id | release    | number |
+----+------------+--------+
|  1 | 2016-07-08 | 4      | 
|  2 | 2016-07-09 | 1      | 
|  3 | 2016-07-10 | 1      | 
|  4 | 2016-07-12 | 2      | 
|  5 | 2016-07-13 | 1      | 
|  6 | 2016-07-14 | 1      | 
|  7 | 2016-07-15 | 1      | 
|  8 | 2016-07-16 | 2-3    | 
|  9 | 2016-07-17 | 3      | 
| 10 | 2016-07-18 | 1      | 
+----+------------+--------+

select * from version where id > 1 and id < 9;
+----+------------+--------+---------------------------+
| id | release    | number | cumulative_with_condition |
+----+------------+--------+---------------------------+
|  2 | 2016-07-09 | 1      | 1                         |
|  3 | 2016-07-10 | 1      | 2                         |
|  4 | 2016-07-12 | 2      | 0                         |
|  5 | 2016-07-13 | 1      | 1                         |
|  6 | 2016-07-14 | 1      | 2                         |
|  7 | 2016-07-15 | 1      | 3                         |
|  8 | 2016-07-16 | 2-3    | 0                         |
+----+------------+--------+---------------------------+

It's possible, but not straightforward in MySQL (since, unlike some other RDBMS products, it doesn't support analytic functions). You're probably best off implementing this within your application code. — eggyal, Jul 08 '16 at 12:37
Possible duplicate of [MySQL calculation of cumulative sum with a reset condition](http://stackoverflow.com/questions/31885375/mysql-calculation-of-cumulative-sum-with-a-reset-condition) — Shadow, Jul 08 '16 at 12:42

score 1 · Accepted Answer · answered Jul 08 '16 at 12:46

1

You want something like row_number() (not exactly, but like that). You can do that using variables:

select t.*,
       (@rn := if(number = 1, @rn + 1,
                  if(@n := number, 0, 0)
                 )
       ) as cumulative_with_condition
from t cross join
     (select @n := '', @rn := 0) params
order by t.id;

answered Jul 08 '16 at 12:46

Gordon Linoff

1,242,037
58
646
786

I marked this question as a duplicate of another, where you provided exactly the same answer... – Shadow Jul 08 '16 at 12:58

score 1 · Answer 2 · answered Jul 08 '16 at 13:17

1

As an alternative to using user variables, as demonstrated by Gordon Linoff, in this case it's also possible to self-join, group and count:

SELECT   t.id, t.release, t.number, COUNT(version.id) AS cumulative_with_condition
FROM     version RIGHT JOIN (
           SELECT   highs.*, MAX(lows.id) min
           FROM     version lows RIGHT JOIN version highs ON lows.id <= highs.id
           WHERE    lows.number <> '1'
           GROUP BY highs.id
         ) t ON version.id > t.min AND version.id <= t.id
WHERE    t.id > 1 AND t.id < 9
GROUP BY t.id

See it on sqlfiddle.

But, frankly, neither approach is particularly elegant—as I commented previously, you're probably best off implementing this within your application code.

answered Jul 08 '16 at 13:17

eggyal

122,705
18
212
237

Thanks for your answer. About elegance, my concern is with optimization. In your opinion, would be optimized implement in my app or use your approach? – Sant Jul 08 '16 at 13:59
@fenix: You'd do better to be concerned with maintainability. Remember Knuth's maxim, "*premature optimisation is the root of all evil.*" – eggyal Jul 08 '16 at 16:05
Very thanks, your suggestion is very valuable at this stage. – Sant Jul 08 '16 at 16:17

SQL Query Conditional accumulation

2 Answers2