I have the following table:

id	student	period	point
1	1	Q1	0
2	2	Q1	2
3	2	Q2	5
4	2	Q3	0
5	3	Q1	7
6	3	Q1	8
7	3	Q2	3
8	3	Q2	1
9	3	Q3	0
10	3	Q3	0
11	4	Q1	1
12	4	Q3	9

I want to know that in which period which student has the most points in total.

---

When I execute this query:

SELECT
    MAX(SUM(point)) score,
    student,
    `period`
FROM table1
GROUP BY student, `period`

it gives the following error:

#1111 - Invalid use of group function

---

When I execute this query:

SELECT
    `period`,
    student,
    MAX(p) score
FROM
(
    SELECT
        SUM(point) p,
        student,
        `period`
    FROM table1
    GROUP BY student, `period`
) t1
GROUP BY `period`

it gives the following result:

period	student	score
Q1	1	15
Q2	1	5
Q3	1	9

The periods and their max points are good, but I always have the first student id.

---

Expected output:

period	student	score
Q1	3	15
Q2	2	5
Q3	4	9

On top of that. If there is more than one student with the highest points, I want to know all of them.

CodePudding user response：

You could use max window function as the following:

WITH sum_pt AS
(
    SELECT student, period,
    SUM(point) AS st_period_pt
    FROM table1
    GROUP BY student, period
),
max_sum as
(
  SELECT *,
    MAX(st_period_pt) OVER (PARTITION BY period) AS max_pt_sum
  FROM sum_pt
)
SELECT student, period,  st_period_pt
FROM max_sum
WHERE st_period_pt = max_pt_sum
ORDER BY period

See demo.

CodePudding user response：

Try with window functions:

• SUM, to get the total points for each <student, period> pair
• ROW_NUMBER, to rank points for each period

Then you can select where ranking = 1 to get your highest points for each period.

WITH students_with_total_points AS (
    SELECT *, SUM(point) OVER(PARTITION BY student, period) AS total_points 
    FROM tab
), ranking_on_periods AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY period ORDER BY total_points DESC) AS rn 
    FROM students_with_total_points
)
SELECT id, student, period, total_points
FROM ranking_on_period
WHERE rn = 1

CodePudding user response：

You could use left join as follows :

select t1.period, t1.student, t1.score
from (
    select student, period, score
    from (
        select student, period, SUM(point) as score 
        from table1 s
        group by student, period
    ) as s
    group by period, student
) as t1
left join (
    select student, period, score
    from (
        select student, period, SUM(point) as score 
        from table1 s
        group by student, period
    ) as s
    group by period, student
) as t2 on t1.student = t2.student and t1.score < t2.score
where t2.score is null;

This query will list also students and their periods if there scores is 0, you can excludes them by adding where close in t1 and t2 temp tables.