Select语句中的Count的平均值

Question

I can't seem to solve this riddle. I have the following statement:

SELECT schedule_test.ID, LEFT(schedule_test.Course,2) AS 'Type', COUNT(*) AS  
'Count'
FROM schedule_test
GROUP BY ID, Type

It echos the following:

What can I add to the Select statement to add a 4th column with the average of the count for each Type? In other words, what was the average count of Type "HS" across all employees? Tried adding AVG(COUNT(*)) but I get an error, Invalid use of group function.

Answer 1

You can try this:

SELECT .......,
AVG(Count) AS average,
AVG(Count WHEN Type = 'HS' THEN Count ELSE NULL END) AS conditionalAverage
FROM schedule_test
GROUP BY ID, Type;