MySQL cumulative sum query not returning expected results

Question

I am trying to create a MySQL query (MySQL v5) that totals call records by day/inbound number with a running cumulative total. I have referenced other pages on Stack Overflow but the results I'm getting are not adding up.

References: MYSQL cumulative sum by date MySQL cumulative sum order by date

The query looks like so:

SET @RUNNING_TOTAL :=0;
SELECT
    DATE_FORMAT(start,'%d/%m/%Y') As CallDate,
    ch.did AS InboundNo,
    COUNT(*) AS DayTotal,
    (@RUNNING_TOTAL := @RUNNING_TOTAL + COUNT(*)) AS CumulativeCalls
FROM
    `call_history` ch
LEFT JOIN (SELECT callid, event FROM ast_queue_log WHERE event = 'ENTERQUEUE') aql ON aql.callid = ch.callid
WHERE
    ch.did = '01234567891' AND
    start BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
GROUP BY
    ch.did, DATE(start)
ORDER BY
    ch.did;

I would expect the following output:

+-------------------------------+-------------+----------+-----------------+
| CallDate                      | InboundNo   | DayTotal | CumulativeCalls |
+-------------------------------+-------------+----------+-----------------+
| 01/05/2020                    | 01234567891 |      232 |             232 |
| 02/05/2020                    | 01234567891 |       50 |             282 |
| 03/05/2020                    | 01234567891 |       14 |             296 |
| 04/05/2020                    | 01234567891 |      246 |             542 |
| 05/05/2020                    | 01234567891 |      187 |             729 |
| 06/05/2020                    | 01234567891 |      182 |             911 |
| 07/05/2020                    | 01234567891 |      105 |            1016 |
| 08/05/2020                    | 01234567891 |       46 |            1062 |
| 09/05/2020                    | 01234567891 |       26 |            1088 |
| 10/05/2020                    | 01234567891 |        7 |            1095 |
| 11/05/2020                    | 01234567891 |      255 |            1350 |
+-------------------------------+-------------+----------+-----------------+

What I am getting is the same values in DayTotal and CumulativeCalls for each day.

@btongeorge can you post the SQL scripts to create your tables and populate them with some data? — Gharbad The Weak, May 27 '20 at 15:24
*References: MYSQL cumulative sum by date MySQL cumulative sum order by date* The reference deals with the task which does not use GROUP BY, and so it is not applicable in your case. — Akina, May 27 '20 at 16:02

Gordon Linoff · Accepted Answer · 2020-05-27T15:32:17.427

0

In MySQL 8+, you should be using window functions:

SELECT DATE_FORMAT(DATE(start), '%d/%m/%Y') As CallDate,
       ch.did AS InboundNo,
       COUNT(*) AS DayTotal,
       SUM(COUNT(*)) OVER (PARTITION BY ch.did ORDER BY DATE(start)) as  CumulativeCalls
FROM call_history ch LEFT  JOIN
     ast_queue_log aql
     ON aql.event = 'ENTERQUEUE' AND aql.callid = ch.callid
WHERE ch.did = '01234567891' AND
      start BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW()
GROUP BY ch.did, DATE(start)
ORDER BY ch.did;

Notes:

The subquery is not needed for the LEFT JOIN.
All columns should be qualified. What table does start come from?
The GROUP BY and SELECT are consistent, by using DATE(start) in the SELECT statement.

In older versions of MySQL, you'll need variables and a subquery:

SELECT dc.*,
       (@s := @s + DayTotal) as CumulativeCalls
FROM (SELECT DATE_FORMAT(DATE(start), '%d/%m/%Y') As CallDate,
             ch.did AS InboundNo,
             COUNT(*) AS DayTotal
      FROM call_history ch LEFT  JOIN
           ast_queue_log aql
           ON aql.event = 'ENTERQUEUE' AND aql.callid = ch.callid
      WHERE ch.did = '01234567891' AND
            start BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW()
      GROUP BY ch.did, DATE(start)
      ORDER BY ch.did, DATE(start)
     ) dc CROSS JOIN
     (SELECT @s := 0) params;

edited May 27 '20 at 15:32

answered May 27 '20 at 15:25

Gordon Linoff

1,242,037
58
646
786

Thanks - can you please explain the additional logic? – btongeorge May 27 '20 at 15:33
I think you must move the ordering from subquery into outer query. If server decides to cache the subquery result you may get records in mixed order easily. – Akina May 27 '20 at 16:01
@Akina . . . No. When using variables, you need to order in the subquery, relying on MySQL to materialize the results. Variables do not necessarily work with the ordering in the outer query. – Gordon Linoff May 27 '20 at 19:08
@GordonLinoff You do NOT use variable in subquery. You use it in outer query. *relying on MySQL to materialize the results* Outer sorting do the same. PS. I am not sure that sorting in the subquery causes its result materializing unconditionally... maybe you have seen something about this in the documentation? – Akina May 27 '20 at 19:09
@Akina . . . You've pretty much described the situation. The data needs to be sorted and materialized for MySQL to apply variables correctly. Fortunately, this is no longer an issue and variables are deprecated for this purpose. – Gordon Linoff May 27 '20 at 20:07

MySQL cumulative sum query not returning expected results

1 Answers1