Listing 5 largest users, measured by most ordinary rows in a foreign key table

Question

Here is a picture of my database structure:

When I enter Observation , I enter a line in Observations , as well as 0, 1 or more lines in Criteria .

I am trying to write an SQL statement that allows me to select the strongest 5 teachers by certain criteria .

So, for example, I would click on Questioning (which can have an ID 5 in Criteria_Labels ), I would like to return a list of 5 teachers ( Teacher_ID from Observations ), who have the largest number of lines Criteria_ID = 5 in Criteria .

The statement I tried to write is as follows:

 SELECT t.Name AS Teacher_Name FROM observations o LEFT JOIN teachers t ON o.Teacher_ID = t.Teacher_ID LEFT JOIN criteria c ON o.ID = c.Observation_ID WHERE c.Criteria_ID = 5 ORDER BY COUNT(c.Criteria_ID) DESC LIMIT 0,5 

However, it seems that only one person is returning. I'm not sure that everything is fine with me, but I hope that I am correct.

Can anyone help? Thanks in advance,

Answer 1

 SELECT t.Teacher_ID, t.Name AS Teacher_Name, count(*) as total FROM observations o LEFT JOIN teachers t ON o.Teacher_ID = t.Teacher_ID LEFT JOIN criteria c ON o.ID = c.Observation_ID WHERE c.Criteria_ID = 5 group by t.Teacher_ID, t.Name order by total desc limit 5