Reputation: 4998
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.
Upvotes: 2
Views: 427
Reputation: 164089
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
Upvotes: 2
Reputation: 169
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
Upvotes: 1