Ken Stone21,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?
Ben DeitchTreehouse Teacher
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 )
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 Mergulhao3,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