Mysql Question posted in
The official MySQL documentation can be found here.

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 ....  
->  
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  
->  
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  
->  
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  
->  
2021-09-07,73,82,58,69
Tags:

2

Answers


  1. 0

    Proof of Work

    Proof of Work GitHub Input and Output from SQL Online Compiler: https://gist.github.com/xentzenith/ccdcc3e11f377195645cf2db8a4dccf0

    To achieve your objective of comparing the mark obtained by a student against the highest, lowest, and average class marks in a particular subject for each day, you can use a combination of window functions and subqueries.

    WITH stats AS (
    SELECT
        date,
        subject,
        MAX(mark) OVER (PARTITION BY date, subject) AS highest,
        MIN(mark) OVER (PARTITION BY date, subject) AS lowest,
        AVG(mark) OVER (PARTITION BY date, subject) AS avg
    FROM perf
)
SELECT
    p.date,
    p.mark,
    s.highest,
    s.lowest,
    ROUND(s.avg, 0) AS avg,
    p.student
FROM perf p
JOIN stats s ON p.date = s.date AND p.subject = s.subject
WHERE p.mark = s.mark;

    The stats CTE computes the highest, lowest, and average marks for each subject on each date using window functions (MAX(), MIN(), AVG()). The PARTITION BY clause specifies the group of rows for which the aggregate function is applied. It should work for you if you are using appropriate SQL version 🙂

    Login or Signup to reply.
  2. 0

    Use a CTE to calculate the overall values and join to that:

    with cte as (
  SELECT
    date,
    subject,
    MAX(mark) AS highest,
    MIN(mark) AS lowest,
    FORMAT(AVG(c.mark), 0) AS avg
  FROM perf
  GROUP BY date, subject  
)
SELECT
    c.date,
    c.subject,
    p.mark,
    c.highest,
    c.lowest,
    c.avg,
    p.student
FROM perf p
JOIN cte c ON c.date = p.date
  AND c.subject = p.subject
WHERE c.mark = p.mark
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search