Search code examples
sqlselectmariadbsubquerymax

SUM and MAX function in SQL with multiple group by clause causes issue

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.

Solution

  • 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.