Databases SQL Reporting by Example Day 3: Getting Good at Grouping Busiest Teachers

Ken Stone
Ken Stone
21,726 Points

Alias the count

I found that aliasing the count can make it a bit more legible.

select count(*) as num_classes, teacher_id, teachers.first_name, teachers.last_name
from teachers
join classes on classes.teacher_id = teachers.id
group by teacher_id having num_classes = 7
order by num_classes desc;

Is there a way to find out the max(num_classes)? besides order by and looking at the results? Then aliasing max instead of having to hard code the number 7 in the query?

3 Answers

Ben Deitch
STAFF
Ben Deitch
Treehouse Teacher

Sure!

WITH NUM_CLASSES_COUNT AS (
  select count(*) as num_classes, teacher_id, teachers.first_name,  teachers.last_name
  from teachers
  join classes on classes.teacher_id = teachers.id
  group by teacher_id
  order by num_classes desc
)
SELECT * FROM NUM_CLASSES_COUNT
WHERE NUM_CLASSES = (
  SELECT MAX(NUM_CLASSES) 
  FROM NUM_CLASSES_COUNT
)
Ken Stone
Ken Stone
21,726 Points

Thanks again! Yes I asked too soon as WITH is covered in the next set of videos!

Does this code work as well?

SELECT FIRST_NAME, LAST_NAME FROM TEACHERS JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID WHERE PERIOD_ID IN (1, 2, 3, 4, 5, 6, 7) GROUP BY TEACHERS.ID;

Guilherme Mergulhao
Guilherme Mergulhao
3,980 Points

This is the way I managed to do:

SELECT T.FIRST_NAME || ' ' || T.LAST_NAME AS FULL_NAME, COUNT(C.PERIOD_ID) AS CLASSES
FROM CLASSES AS C
INNER JOIN TEACHERS AS T ON T.ID = C.TEACHER_ID
GROUP BY T.FIRST_NAME
HAVING CLASSES = 7
ORDER BY FULL_NAME ASC