Question :
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 |
+------------+------------+-----------+-------+
Answer :
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 !!!
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.
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.