mysqlgroup-bysubquerycommon-table-expression

How do I incorporate a row into a GROUP BY query in MySQL?


I have a table for recording the mark a student got in a subject on a particular date. The table, perf, has columns id,date,student,subject,mark.

Objective: I want to compare the mark obtained by a student against the highest, the lowest and the average class mark in a particular subject for each day using a single query such as:

SELECT date,mark,highest,lowest,avg,student FROM ....   

.... and get this result:

date mark highest lowest avg student
2021-09-07 73 82 58 69 2c3
2021-09-09 81 84 62 75 2c3
2021-09-14 78 78 68 73 2c3
2021-09-17 75 89 59 73 2c3

How do I achieve my objective in a single statement?

I am open to intermediate steps, eg, creating views.

I am using MySQL 8.0.33.

Here is the data:

id date student subject mark
1 2021-09-6 a1b English 78
2 2021-09-7 a1b Art 63
3 2021-09-8 a1b Math 67
4 2021-09-09 a1b Art 71
5 2021-09-09 a1b English 74
6 2021-09-10 a1b Math 65
7 2021-09-13 a1b English 81
8 2021-09-14 a1b Art 68
9 2021-09-15 a1b Math 70
10 2021-09-16 a1b English 79
11 2021-09-17 a1b Art 70
12 2021-09-17 a1b Math 68
14 2021-09-6 2c3 English 68
15 2021-09-7 2c3 Art 73
16 2021-09-8 2c3 Math 57
17 2021-09-09 2c3 Art 81
18 2021-09-09 2c3 English 74
19 2021-09-10 2c3 Math 55
20 2021-09-13 2c3 English 73
21 2021-09-14 2c3 Art 78
22 2021-09-15 2c3 Math 60
23 2021-09-16 2c3 English 71
24 2021-09-17 2c3 Art 75
25 2021-09-17 2c3 Math 58
26 2021-09-6 3d4 English 53
27 2021-09-7 3d4 Art 58
28 2021-09-8 3d4 Math 56
29 2021-09-09 3d4 Art 62
30 2021-09-09 3d4 English 54
31 2021-09-10 3d4 Math 51
32 2021-09-13 3d4 English 51
33 2021-09-14 3d4 Art 68
34 2021-09-15 3d4 Math 60
35 2021-09-16 3d4 English 58
36 2021-09-17 3d4 Art 59
37 2021-09-17 3d4 Math 58
38 2021-09-6 4ef English 87
39 2021-09-7 4ef Art 82
40 2021-09-8 4ef Math 91
41 2021-09-09 4ef Art 84
42 2021-09-09 4ef English 79
43 2021-09-10 4ef Math 81
44 2021-09-13 4ef English 73
45 2021-09-14 4ef Art 78
46 2021-09-15 4ef Math 82
47 2021-09-16 4ef English 82
48 2021-09-17 4ef Art 89
49 2021-09-17 4ef Math 92

I am able to get the highest, lowest, average class mark using GROUP BY as follows:

SELECT date,subject,MAX(mark) AS highest, MIN(mark) AS lowest, FORMAT(AVG(mark),0) AS avg FROM perf GROUP BY date,subject   
date mark highest lowest avg
2021-09-06 English 87 53 72
2021-09-07 Art 82 58 69
2021-09-08 Math 91 56 68
2021-09-09 Art 84 62 75
2021-09-09 English 79 54 70
2021-09-10 Math 81 51 63

On trying to incorporate mark and student into the above query gives senseless results:

SELECT date,subject,mark,MAX(mark) AS highest, MIN(mark) AS lowest, FORMAT(AVG(mark),0) AS avg,student FROM perf GROUP BY date,subject,mark,student  
date subject mark highest lowest avg student
2021-09-16 English 71 71 71 71 2c3
2021-09-07 Art 73 73 73 73 2c3
2021-09-13 English 73 73 73 73 2c3
2021-09-13 English 73 73 73 73 4ef
2021-09-09 English 74 74 74 74 a1b
2021-09-09 English 74 74 74 74 2c3
2021-09-17 Art 75 75 75 75 2c3
2021-09-06 English 78 78 78 78 a1b

I tried several things but have only been successful in combining mark,highest,lowest,avg,student in a single result when I specify student,subject and date as follows, but that's too specific and not what I want:

SELECT (SELECT date FROM perf WHERE student='2c3' AND subject='Art' AND date='2021-09-07') AS date,  
(SELECT mark FROM perf WHERE student='2c3' AND subject='Art' AND date='2021-09-07') AS mark,  
(SELECT MAX(mark) FROM perf WHERE subject='Art' AND date='2021-09-07') AS highest,  
(SELECT MIN(mark) FROM perf WHERE subject='Art' AND date='2021-09-07') AS lowest,  
(SELECT FORMAT(AVG(mark),0) FROM perf WHERE subject='Art' AND date='2021-09-07') AS avg 
date mark highest lowest avg
2021-09-07 73 82 58 69

Solution

  • SELECT
    a.DATE,
    b.lowest,
    b.highest,
    b.avg,
    a.student,
    a.mark
    FROM perf a
    LEFT JOIN (
    SELECT
    DATE,
    SUBJECT, 
    MIN(mark) AS lowest,
    MAX(mark) AS highest,
    AVG(mark) AS AVG
    FROM perf
    GROUP BY DATE,SUBJECT ) b ON a.date = b.date AND a.subject = b.subject