Cumulative sum with custom sorting in MySQL

Question

I have a table that looks like this

id   remaining   expiry_date
1    200         2019-11-15
2     10         2019-11-23
3     10         2019-11-16
4     10         2019-11-16
5      7         2019-11-16

I want to fetch the results that have a running total of 215 that is sorted by expiry_date in ascending order.

What I am able to achieve so far?

SELECT *, @sum := (@sum + remaining) AS csum 
FROM tickets 
JOIN (SELECT @sum := 0) r 
WHERE @sum < 215 
ORDER BY id;

This query returns the following result which is correct.

id   remaining   expiry_date   csum
1    200         2019-11-15    200
2     10         2019-11-23    210
3     10         2019-11-16    220

But when I try to sort it with expiry_date it returns all the records.

SELECT *, @sum := (@sum + remaining) AS csum 
FROM tickets 
JOIN (SELECT @sum := 0) r 
WHERE @sum < 215 
ORDER BY expiry_date;

Result:

id   remaining   expiry_date   csum
1    200         2019-11-15    200
3    10          2019-11-16    210
4    10          2019-11-16    220
5    7           2019-11-16    227
2    10          2019-11-23    237

The sorting is correct but the result is way more than I need.

What I want

I want to return the following result.

id   remaining   expiry_date   csum
1    200         2019-11-15    200
3    10          2019-11-16    210
4    10          2019-11-16    220

Also, the number 215 can change dynamically so the number of rows returned can vary based on that number. How can I change the query so I can achieve this?

Edit

I apologize for not being clear with what I actually wanted in my result set. Please let me clarify with this edit. I don't want the records with running-total less than the given amount. I want the records until the running-total is equal to or exceeds the given amount.

You seem to want 'the (highest or all) running total less than or equal to 215' - except that 220 is greater than 215 !?!?! — Strawberry, Nov 07 '19 at 11:55
@Strawberry I wanted the records with running total until it passes the sum of 215 hence the records with sum 220. — Lalit Thapa, Nov 07 '19 at 12:03
I don't have experience with variables in MySQL. I suppose that the `ORDER BY` comes to late somehow. Don't know. What's your MySQL version? As of MySQL 8 you would use window functions as in other modern DBMS, instead of muddling through with variables. — Thorsten Kettner, Nov 07 '19 at 12:12

Thorsten Kettner · Accepted Answer · 2019-11-07T13:09:02.140

First of all there are multiple entries per date. So the date alone doesn't suffice to get a stable sort order. I suggest ORDER BY expiry_date, id to get this straight.

Then, a running total would be done with window functions in any modern RDBMS. They are available in MySQL as of version 8.

select id, remaining, expiry_date, csum
from
(
  select
    id, remaining, expiry_date, 
    sum(remaining) over (order by expiry_date, id) as csum,
    sum(remaining) over (order by expiry_date, id 
                         rows between unbounded preceding and 1 preceding) as lag_csum
  from tickets
) summed
where coalesce(lag_csum, 0) < 215
order by expiry_date, id;

If window functions are not available, you can use a correlated aggregating subquery instead. That's probably much slower, but should work just as well.

select *
from
(
  select
    id, remaining, expiry_date,
    (
      select sum(remaining)
      from tickets t2
      where t2.expiry_date < t1.expiry_date
        or (t2.expiry_date = t1.expiry_date and t2.id <= t1.id)
    ) as csum,
    (
      select sum(remaining)
      from tickets t2
      where t2.expiry_date < t1.expiry_date
        or (t2.expiry_date = t1.expiry_date and t2.id < t1.id)
    ) as lag_csum
  from tickets t1
) summed
where coalesce(lag_csum, 0) < 215
order by expiry_date, id;

Both queries are standard SQL and are thus not restricted to MySQL.

It seems to work but where do you put the extra conditional check. Like what if I only need to get the records that haven't been expired yet? — Lalit Thapa, Nov 08 '19 at 04:48
In a where clause of course. If these rows must be considered in order to get correct running totals, then you must apply the condition after getting them, i.e. in the main query. If these rows must not be considered in order to get correct running totals, then you must apply the condition before getting them, i.e. in the first query's subquery or the second queries' innermost subqueries. — Thorsten Kettner, Nov 08 '19 at 06:12

score 0 · Answer 2 · edited Nov 07 '19 at 19:35

0

Try using limit 3 and this result as subquery for a new order:

SELECT * 
FROM
    (SELECT *, @sum := (@sum + remaining) AS csum 
     FROM tickets 
     JOIN (SELECT @sum := 0) r 
     WHERE @sum < 215 
     ORDER BY id 
     LIMIT 3) t 
ORDER BY expiry_date

or based on you updated question could be you need only the last 3 from order by date

SELECT *, @sum := (@sum + remaining) AS csum 
FROM tickets 
JOIN (SELECT @sum := 0) r 
WHERE @sum < 215 
ORDER BY expiry_date
LIMIT 3;

Otherwise If you don't want use limit but you want filter the result for csum then you could try using your query as a subquery and filter for the value you want eg: 225

SELECT * 
FROM
    (SELECT t.*, @sum := (@sum + t.remaining) AS csum 
     FROM tickets t
     JOIN (SELECT @sum := 0) r 
     ORDER BY expiry_date ) t1 
WHERE t1.csum < 225

Check

SELECT * 
FROM
    (SELECT t.*, @sum := (@sum + t.remaining) AS csum 
     FROM 
         (SELECT 1 id, 200 remaining, '2019-11-15' expiry_date
          UNION ALL
          SELECT 2, 10, '2019-11-23'
          UNION ALL
          SELECT 3, 10, '2019-11-16'
          UNION ALL
          SELECT 4, 10, '2019-11-16'
          UNION ALL 
          SELECT 5, 7, '2019-11-16') t
     JOIN (SELECT @sum := 0) r 
     ORDER BY expiry_date ) t1 
WHERE t1.csum < 225

edited Nov 07 '19 at 19:35

marc_s

732,580
175
1,330
1,459

answered Nov 07 '19 at 09:25

ScaisEdge

131,976
10
91
107

it returns the result in order (id) `1, 3, 2` from the original table but I want it in order `1, 3, 4`. And I don't really want to use limit as the number `215` can change dynamically so the returned results can be any number of rows. – Lalit Thapa Nov 07 '19 at 10:19
As I mentioned in my comment I don't really want to use limit because the condition query can change dynamically. Using limit just solves for this particular condition what if I want to change **215** to **225** then it would return me 3 records but I would want 4th and 5th records too. And I can't limit the records dynamically. – Lalit Thapa Nov 07 '19 at 10:52
That doesn't give me the expected result as well. – Lalit Thapa Nov 07 '19 at 11:05
answer updated .. with a sample for check the valid result – ScaisEdge Nov 07 '19 at 11:44
I tried those types of queries as well but it would always give me one less record. Anyway thanks for trying. I found a way to make it work. – Lalit Thapa Nov 07 '19 at 12:00
in my sample you can clearly see the return the valid result .. so check better .. – ScaisEdge Nov 07 '19 at 12:01
Yes, it certainly returns the expected result just not what I was expecting. I wanted to get the records that have to reach the sum x if it can. Your last query would always return one less record as it would return records less than x. Apologies if I wasn't clear enough on my question. – Lalit Thapa Nov 07 '19 at 12:07

Cumulative sum with custom sorting in MySQL

2 Answers2