MySQL: SUM(CASE WHEN(...)) + GROUP BY. Why details of dataset cannot show even with GROUP BY?

Question

Credit: Leetcode 1308. Running Total for Different Genders

Sample Score table:

+-------------+--------+------------+--------------+
| player_name | gender | day        | score_points |
+-------------+--------+------------+--------------+
| Aron        | F      | 2020-01-01 | 17           |
| Alice       | F      | 2020-01-07 | 23           |
| Bajrang     | M      | 2020-01-07 | 7            |
| Khali       | M      | 2019-12-25 | 11           |
| Slaman      | M      | 2019-12-30 | 13           |
| Joe         | M      | 2019-12-31 | 3            |
| Jose        | M      | 2019-12-18 | 2            |
| Priya       | F      | 2019-12-31 | 23           |
| Priyanka    | F      | 2019-12-30 | 17           |
+-------------+--------+------------+--------------+

Requirement: Write an SQL query to find the (cumulative) total score for each gender at each day. Order the result table by gender and day. The sample Result table is as follows:

+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+

My code is as follows. I cannot figure out why it only returns one row[{"headers": ["gender", "day", "total"], "values": [["F", "2019-12-30", 184]]}], even with GROUP BY.

SELECT s1.gender, s1.day, 
SUM(CASE WHEN s1.day < s2.day AND s1.gender = s2.gender THEN s1.score_points ELSE 0 END) AS total
FROM Scores s1, Scores s2
GROUP BY s1.gender AND s1.day
ORDER BY s1.gender AND s1.day

Thank you so much if anyone can help me out!!

Answer 1

You should do a proper join with an ON clause and use valid syntax for GROUP BY and ORDER BY:

select s1.gender, s1.day, sum(s2.score_points) total
from scores s1 inner join scores s2
on s2.gender = s1.gender and s2.day <= s1.day
group by s1.gender, s1.day
order by s1.gender, s1.day

See the demo.
Results:

| gender | day        | total |
| ------ | -----------| ----- |
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |