SQL Server Average Query

Question

Can somebody help me about how to query the average of this?

I have this table.

 teacher | class | students | rating
 -----------------------------------
   T1        1      001        6
   T1        1      002        6
   T1        2      003        1
   T1        3      004        1
   T2        1      001        6
   T2        1      002        6
   T2        2      003        1
   T2        3      004        1

if I do Select teacher, class, avg(rating) from table group by teacher, class then I get this:

teacher | class | rating
------------------------
   T1       1     6
   T1       2     1
   T1       3     1
   T2       1     6
   T2       2     1
   T2       3     1

what I want to do is get the average rating of that result and group it by teacher which is:

teacher | rating
------------------------
   T1      2.66667
   T2      2.66667

because if i do Select avg(rating) from table group by teacher, I get

teacher | rating
------------------------
   T1      3.5
   T2      3.5

Answer 1

You can do this:

SELECT teacher,avg(avgrating) as avgrating
FROM
(Select teacher, class, avg(rating) avgrating
from TableName 
group by teacher, class) Temp
GROUP BY teacher

Result:

TEACHER   AVGRATING
T1        2.666666
T2        2.666666

Sample result in SQL Fiddle.

OR:

SELECT T1.teacher,avg(T2.avgrating) as rating
FROM TableName T1 JOIN
(SELECT teacher,class,avg(rating) as avgrating
 FROM TableName T2
 GROUP BY teacher,class) T2 ON T1.teacher=T2.teacher
GROUP BY T1.teacher

SQL Fiddle.

Answer 2

use a nested select:

select teacher , avg(avgr) from
 (Select teacher, class, avg(rating) avgr from table group by teacher, class) qry
group by teacher