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