Combine two queries to one 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&amp;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 |
+------------+------------+-----------+-------+
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.