How to calculate running sum for each group in MySQL

Question

Straightforward summation I can do with sum() function. But I've a different situation here. I've a table Student with only 2 fields. For instance just assume that there is only 1 student in the entire class:

CREATE TABLE student
    (`dateOfExam` date, score int)
;
    
INSERT INTO student
    (`dateOfExam`, `score`)
VALUES
    ('2020-05-28',5),
    ('2020-05-29',5),
    ('2020-05-30',10),
    ('2020-06-03',10),
    ('2020-06-05',5),
    ('2020-07-21',20),
    ('2020-07-22',10),
    ('2020-07-28',10)
;

And I have his scores for the days when exams were taken with one more column in the runtime which is the month in which exam was held:

The query is (took help from stackoverflow yesterday):

select date_format(dateOfExam, '%Y-%m') ExamMonth
     , dateOfExam
     , score 
  from student;

Result:

+-----------+------------+-------+
| ExamMonth | dateOfExam | score |
+-----------+------------+-------+
| 2020-05   | 2020-05-28 |     5 |
| 2020-05   | 2020-05-29 |     5 |
| 2020-05   | 2020-05-30 |    10 |
| 2020-06   | 2020-06-03 |    10 |
| 2020-06   | 2020-06-05 |     5 |
| 2020-07   | 2020-07-21 |    20 |
| 2020-07   | 2020-07-22 |    10 |
| 2020-07   | 2020-07-28 |    10 |
+-----------+------------+-------+

My requirement is I want to reward this student every month. I'll keep on adding his score for each date for each individual month and give him Reward1 when accumulated score sum reaches 10 and Reward2 when accumulated score sum reaches 20. So the final table should like this:

+---------------+---------------+-------+---------------+---------------+
| ExamMonth     |  dateOfExam   | Score |    Reward1    |   Reward2     |
+---------------+---------------+-------+---------------+---------------+
|    2020-05    |  2020-05-28   |   5   |               |               |
|               |  2020-05-29   |   5   |       Y       |               |
|               |  2020-05-30   |   10  |               |       Y       |
|---------------|---------------|-------|---------------|---------------|
|    2020-06    |  2020-06-03   |   10  |       Y       |               |
|               |  2020-06-05   |   5   |               |               |
|---------------|---------------|-------|---------------|---------------|
|    2020-7     |  2020-07-21   |   20  |       Y       |       Y       |
|               |  2020-07-22   |   10  |               |               |
|               |  2020-07-28   |   10  |               |               |
+---------------+---------------+-------+---------------+---------------+

Reward fields can be boolean and empty reward rows can be set to N or False or whatever seems logical. This was not helpful: Calculate running sum

Please help me achieve this objective. Suggest some approach.

Here is a fiddle.

forpas · Accepted Answer · 2020-09-21T06:50:51.260

2

First calculate the running sum of the scores for each month in a CTE.
Then apply your conditions:

with cte as (
  select date_format(dateOfExam, '%Y-%m') ExamMonth,
         dateOfExam, score, 
         sum(score) over (partition by date_format(dateOfExam, '%Y-%m') order by dateOfExam) total
  from student
)
select ExamMonth, dateOfExam, score, 
       case when sum(total >= 10) over (partition by ExamMonth order by dateOfExam) = 1 then 'Y' end Reward1,
       case when sum(total >= 20) over (partition by ExamMonth order by dateOfExam) = 1 then 'Y' end Reward2
from cte

See the demo.
Results:

> ExamMonth | dateOfExam | score | Reward1 | Reward2
> :-------- | :--------- | ----: | :------ | :------
> 2020-05   | 2020-05-28 |     5 | null    | null   
> 2020-05   | 2020-05-29 |     5 | Y       | null   
> 2020-05   | 2020-05-30 |    10 | null    | Y      
> 2020-06   | 2020-06-03 |    10 | Y       | null   
> 2020-06   | 2020-06-05 |     5 | null    | null   
> 2020-07   | 2020-07-21 |    20 | Y       | Y      
> 2020-07   | 2020-07-22 |    10 | null    | null   
> 2020-07   | 2020-07-28 |    10 | null    | null

edited Sep 21 '20 at 06:50

answered Sep 21 '20 at 06:34

forpas

160,666
10
38
76

This is exactly what i am looking for. But can't we do it without CTE or join ? I'm doubtful it will work with Sisense. – Tanzeel Sep 21 '20 at 07:36
Because what i asked you is just an excerpt. I've a huge query already, there I've to integrate this. – Tanzeel Sep 21 '20 at 07:38
It can be done without a CTE, with a subquery: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=37a830b127c0625fa3dfa660368538ad – forpas Sep 21 '20 at 07:42
Thanks. One last request. Please tell me why `(partition by ExamMonth order by dateOfExam)` is kept equals to `1` ? – Tanzeel Sep 21 '20 at 08:08
1

The expressions `total >= 10` and `total >= 20` inside sum() are evaluated as 1 for true or 0 for false. These expressions are summed. So the 1st time that `total >= 10` returns true the result is 1 and the sum is 1 and the 1st time that `total >= 20` returns true the result is 1 and the sum is 1. It's better to see it here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=485539972dc57692e22fa1899913bfe7 – forpas Sep 21 '20 at 08:15
Just now I encountered a problem. MySQL 8.0 does not currently support partitioning of tables using any storage engine other than InnoDB or NDB. Now what to do :-( – Tanzeel Sep 21 '20 at 11:57

score 1 · Answer 2 · answered Sep 21 '20 at 05:06

1

The following snippet groups by the base query on ExamMonth and then uses a case when decisioning for the values of Reward1 and Reward2. This query is to give you pointers only. Please rewrite as best suits you.


select DERIVED2.ExamMonth, CASE WHEN DERIVED2.Cumul_Score >= 10 THEN 'Y'

ELSE ''
END AS Rewards1,
CASE WHEN DERIVED2.Cumul_Score >= 20 THEN 'Y'

ELSE ''
END AS Rewards2
FROM 
(
select DERIVED1.ExamMonth, SUM(DERIVED1.score) as Cumul_Score 
FROM
(
select date_format(dateOfExam, '%Y-%m') ExamMonth,
       dateOfExam, score
from student
order by dateOfExam
) DERIVED1

GROUP BY ExamMonth

) DERIVED2

answered Sep 21 '20 at 05:06

Ed Bighands

159
8

let me try this out – Tanzeel Sep 21 '20 at 05:07
This is working on my local. Let me show this to my tech lead. Please give me 10 mins. – Tanzeel Sep 21 '20 at 05:09
But i want the _dateOfExams_ also in the final result. I'm trying that. Plz help. – Tanzeel Sep 21 '20 at 05:13
dateOfExams grain is different from the aggregate query grain. dateOfExams are individual data(in separate rows), not sure how that can be put in the same grain as the aggregate output – Ed Bighands Sep 21 '20 at 05:23
try running this: `select DERIVED2.ExamMonth, DERIVED2.dateOfExam, DERIVED2.score, CASE WHEN DERIVED2.Cumul_Score >= 10 THEN 'Y' ELSE '' END AS Rewards1, CASE WHEN DERIVED2.Cumul_Score >= 20 THEN 'Y' ELSE '' END AS Rewards2 FROM ( select DERIVED1.ExamMonth, DERIVED1.dateOfExam, DERIVED1.score, SUM(DERIVED1.score) as Cumul_Score FROM ( select date_format(dateOfExam, '%Y-%m') ExamMonth, dateOfExam, score from student order by dateOfExam ) DERIVED1 GROUP BY dateOfExam ) DERIVED2;` – Tanzeel Sep 21 '20 at 05:28

How to calculate running sum for each group in MySQL

2 Answers2

Linked