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