1

I need to write query that finds the average for each classname, total average for each student, I already wrote two separate query for each one of those, but how to combine to one query?

My 1st query:

select distinct  StudentFirstName,StudentLastName, ClassName, avg(Grade) as 'average for this subject'
from tests 
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName,ClassName;

My 2nd query:

select distinct  StudentFirstName,StudentLastName, avg(Grade) as 'total average'
from tests 
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName;

Students table:

  pk-INT           VARCHAR             VARCHAR
+-----------+------------------+-----------------+
| StudentID | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
|         1 | agam             | rafaeli         |
|         2 | amir             | aizinger        |
|         3 | avi              | caspi           |
|         4 | avia             | wolf            |
|         5 | ben              | moskovich       |
|         6 | chen             | segalovich      |
|         7 | dana             | levy            |
|         8 | daniel           | marcus          |
|         9 | daphna           | chwarts         |
|        10 | david            | cohen           |
+-----------+------------------+-----------------+

Tests table:

  PK-VARCHR    PK-VARCHR     PK&FK-INT   INT
+------------+------------+-----------+-------+
| TestDate   | ClassName  | StudentID | Grade |
+------------+------------+-----------+-------+
| 2017-07-01 | Algebra    |         1 |    88 |
| 2017-08-02 | Algo       |         1 |    97 |
| 2017-09-01 | Algebra    |         1 |    80 |
| 2017-09-01 | Algebra    |         1 |    97 |
| 2017-09-01 | Set-theory |         1 |    85 |
| 2017-09-04 | Calcules   |         1 |    86 |
| 2016-05-03 | Set-theory |         2 |    84 |
| 2016-07-02 | Calcules   |         2 |    89 |
| 2016-07-04 | Algo       |         2 |    83 |
| 2016-07-05 | Algebra    |         2 |    79 |
| 2016-06-03 | Algebra    |         3 |    99 |
| 2016-07-02 | Algo       |         3 |    97 |
| 2016-07-03 | Calcules   |         3 |    96 |
| 2016-09-03 | Set-theory |         3 |    95 |
| 2016-06-03 | Algebra    |         4 |    78 |
+------------+------------+-----------+-------+
  • 1
    You should specify which RDBMS you're working in, what you expect the final result to look like, and if this is homework its good to mention that too. Posting the table sample as CREATE statements is also helpful so people can quickly test their queries on your data set - SQLFiddle is great for this. – LowlyDBA Dec 4 '17 at 21:38
2

What you are looking for can be done with one query using WITH ROLLUP

PROPOSED QUERY

SELECT BB.StudentFirstName,BB.StudentLastName,
IFNULL(AA.ClassName,
CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
AA.StudentAverage FROM
(select A.StudentID,A.ClassName,avg(Grade) StudentAverage
from tests A inner join students B on A.StudentID=B.StudentID
group by A.StudentID,A.ClassName WITH ROLLUP) AA
inner join students BB on AA.StudentID=BB.StudentID;

SAMPLE DATA

DROP DATABASE IF EXISTS error404;
CREATE DATABASE error404;
USE error404
CREATE TABLE students
(
    StudentID        INT NOT NULL AUTO_INCREMENT,
    StudentFirstName VARCHAR(25),
    StudentLastName  VARCHAR(25),
    PRIMARY KEY (StudentID)
);
INSERT INTO students (StudentFirstName,StudentLastName) VALUES
('agam'  , 'rafaeli'), ('amir'  , 'aizinger'), ('avi'   , 'caspi'),
('avia'  , 'wolf '), ('ben'   , 'moskovich'), ('chen'  , 'segalovich'),
('dana'  , 'levy '), ('daniel', 'marcus'), ('daphna', 'chwarts');
CREATE TABLE tests
(
    testid    INT NOT NULL AUTO_INCREMENT,
    TestDate  DATE,
    ClassName VARCHAR(25),
    StudentID INT NOT NULL,
    Grade     INT NOT NULL,
    PRIMARY KEY (testid),
    KEY (StudentID)
);
INSERT INTO tests (TestDate,ClassName,StudentID,Grade) VALUES
('2017-07-01', 'Algebra'    , 1 , 88 ), ('2017-08-02', 'Algo'       , 1 , 97 ),
('2017-09-01', 'Algebra'    , 1 , 80 ), ('2017-09-01', 'Algebra'    , 1 , 97 ),
('2017-09-01', 'Set-theory' , 1 , 85 ), ('2017-09-04', 'Calcules'   , 1 , 86 ),
('2016-05-03', 'Set-theory' , 2 , 84 ), ('2016-07-02', 'Calcules'   , 2 , 89 ),
('2016-07-04', 'Algo'       , 2 , 83 ), ('2016-07-05', 'Algebra'    , 2 , 79 ),
('2016-06-03', 'Algebra'    , 3 , 99 ), ('2016-07-02', 'Algo'       , 3 , 97 ),
('2016-07-03', 'Calcules'   , 3 , 96 ), ('2016-09-03', 'Set-theory' , 3 , 95 ),
('2016-06-03', 'Algebra'    , 4 , 78 );

PROPOSED QUERY EXECUTED

mysql> SELECT BB.StudentFirstName,BB.StudentLastName,
    -> IFNULL(AA.ClassName,
    -> CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
    -> AA.StudentAverage FROM
    -> (select A.StudentID,A.ClassName,avg(Grade) StudentAverage
    -> from tests A inner join students B on A.StudentID=B.StudentID
    -> group by A.StudentID,A.ClassName WITH ROLLUP) AA
    -> inner join students BB on AA.StudentID=BB.StudentID;
+------------------+-----------------+-------------------------------+----------------+
| StudentFirstName | StudentLastName | ClassName                     | StudentAverage |
+------------------+-----------------+-------------------------------+----------------+
| agam             | rafaeli         | Algebra                       |        88.3333 |
| agam             | rafaeli         | Algo                          |        97.0000 |
| agam             | rafaeli         | Calcules                      |        86.0000 |
| agam             | rafaeli         | Set-theory                    |        85.0000 |
| agam             | rafaeli         | All Classes for agam rafaeli  |        88.8333 |
| amir             | aizinger        | Algebra                       |        79.0000 |
| amir             | aizinger        | Algo                          |        83.0000 |
| amir             | aizinger        | Calcules                      |        89.0000 |
| amir             | aizinger        | Set-theory                    |        84.0000 |
| amir             | aizinger        | All Classes for amir aizinger |        83.7500 |
| avi              | caspi           | Algebra                       |        99.0000 |
| avi              | caspi           | Algo                          |        97.0000 |
| avi              | caspi           | Calcules                      |        96.0000 |
| avi              | caspi           | Set-theory                    |        95.0000 |
| avi              | caspi           | All Classes for avi caspi     |        96.7500 |
| avia             | wolf            | Algebra                       |        78.0000 |
| avia             | wolf            | All Classes for avia wolf     |        78.0000 |
+------------------+-----------------+-------------------------------+----------------+
17 rows in set (0.00 sec)

BONUS QUERY

This query includes all students whether they took a class or not

SELECT BB.StudentFirstName,BB.StudentLastName,
IFNULL(AA.ClassName,
CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
IFNULL(AA.StudentAverage,0) StudentAverage FROM
students BB LEFT JOIN
(select A.StudentID,A.ClassName,avg(Grade) StudentAverage
from tests A inner join students B on A.StudentID=B.StudentID
group by A.StudentID,A.ClassName WITH ROLLUP) AA
on AA.StudentID=BB.StudentID;

BONUS QUERY EXECUTED

mysql> SELECT BB.StudentFirstName,BB.StudentLastName,
    -> IFNULL(AA.ClassName,
    -> CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
    -> IFNULL(AA.StudentAverage,0) StudentAverage FROM
    -> students BB LEFT JOIN
    -> (select A.StudentID,A.ClassName,avg(Grade) StudentAverage
    -> from tests A inner join students B on A.StudentID=B.StudentID
    -> group by A.StudentID,A.ClassName WITH ROLLUP) AA
    -> on AA.StudentID=BB.StudentID;
+------------------+-----------------+---------------------------------+----------------+
| StudentFirstName | StudentLastName | ClassName                       | StudentAverage |
+------------------+-----------------+---------------------------------+----------------+
| agam             | rafaeli         | Algebra                         |        88.3333 |
| agam             | rafaeli         | Algo                            |        97.0000 |
| agam             | rafaeli         | Calcules                        |        86.0000 |
| agam             | rafaeli         | Set-theory                      |        85.0000 |
| agam             | rafaeli         | All Classes for agam rafaeli    |        88.8333 |
| amir             | aizinger        | Algebra                         |        79.0000 |
| amir             | aizinger        | Algo                            |        83.0000 |
| amir             | aizinger        | Calcules                        |        89.0000 |
| amir             | aizinger        | Set-theory                      |        84.0000 |
| amir             | aizinger        | All Classes for amir aizinger   |        83.7500 |
| avi              | caspi           | Algebra                         |        99.0000 |
| avi              | caspi           | Algo                            |        97.0000 |
| avi              | caspi           | Calcules                        |        96.0000 |
| avi              | caspi           | Set-theory                      |        95.0000 |
| avi              | caspi           | All Classes for avi caspi       |        96.7500 |
| avia             | wolf            | All Classes for avia wolf       |        78.0000 |
| avia             | wolf            | Algebra                         |        78.0000 |
| ben              | moskovich       | All Classes for ben moskovich   |         0.0000 |
| chen             | segalovich      | All Classes for chen segalovich |         0.0000 |
| dana             | levy            | All Classes for dana levy       |         0.0000 |
| daniel           | marcus          | All Classes for daniel marcus   |         0.0000 |
| daphna           | chwarts         | All Classes for daphna chwarts  |         0.0000 |
+------------------+-----------------+---------------------------------+----------------+
22 rows in set (0.00 sec)

GIVE IT A TRY !!!

2

That's not possible, because you have different granularity in the two queries. You would have to calculate one average by yourself with a formula like sum(grades) / count(distinct full-student-name). (btw, your use of distinct is not necessary, the group by does that already for you)
With this you would get the count correct, but you would sum too much grades, because they are multiplied by the grouping for classes. You can't do sum(distinct grades) because a student could have the same grade in different classes.
You also can't calculate the average of the averages. That's usually close, but still not correct.

2

Try this solution using correlated sub-queries

SELECT   StudentFirstName,
         StudentLastName,
         ClassName,
         (SELECT Avg(Grade) FROM Tests WHERE ClassName = t.ClassName AND StudentId =s.StudentId) AS StudentClassAverage,
         (SELECT Avg(Grade) FROM Tests WHERE StudentId =s.StudentId) AS StudentAverage
FROM     Students s
         JOIN Tests t
             ON s.StudentId = t.StudentId
GROUP BY s.StudentId

UPDATE 2017-12-05 11:20 EST from RolandoMySQLDBA

Based on Verace's comment, I did set the sql_mode to blank. Here is the output

mysql> SELECT   StudentFirstName,
    ->          StudentLastName,
    ->          ClassName,
    ->          (SELECT Avg(Grade) FROM Tests WHERE ClassName = t.ClassName AND StudentId =s.StudentId) AS StudentClassAverage,
    ->          (SELECT Avg(Grade) FROM Tests WHERE StudentId =s.StudentId) AS StudentAverage
    -> FROM     Students s
    ->          JOIN Tests t
    ->              ON s.StudentId = t.StudentId
    -> GROUP BY s.StudentId;
+------------------+-----------------+------------+---------------------+----------------+
| StudentFirstName | StudentLastName | ClassName  | StudentClassAverage | StudentAverage |
+------------------+-----------------+------------+---------------------+----------------+
| agam             | rafaeli         | Algebra    |             88.3333 |        88.8333 |
| amir             | aizinger        | Set-theory |             84.0000 |        83.7500 |
| avi              | caspi           | Algebra    |             99.0000 |        96.7500 |
| avia             | wolf            | Algebra    |             78.0000 |        78.0000 |
+------------------+-----------------+------------+---------------------+----------------+
4 rows in set (0.00 sec)

mysql>

Although not all subjects appear for every student that took a test, the averages are correct for what was retrieved. The challenge here would be to adjust the query for the averages of every subject to appear. I am going to upvote this because correlated subqueries can be real headaches sometimes but can inevitably do the job.

  • That's not valid syntax SELECT * with GROUP BY. – ypercubeᵀᴹ Dec 4 '17 at 22:33
  • oops - edited... – SEarle1986 Dec 4 '17 at 22:37
  • This does not work in MySQL 5.7 – RolandoMySQLDBA Dec 5 '17 at 1:54
  • 1
    It will if you set sql_mode = ''! :-) Do NOT do this at home, children... – Vérace Dec 5 '17 at 14:11
  • @Vérace I tried it with sql_mode=''. I will post the output. – RolandoMySQLDBA Dec 5 '17 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.