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

Paul Brubaker
Paul Brubaker
5,873 Points

Forgot about HAVING keyword and came up with this monstrosity.

I completely forgot about the HAVING keyword and how it can be used to filter by a condition after grouping, and the alternative was anything but elegant! I won't be forgetting about HAVING again!

WITH teacher_workload AS (SELECT TEACHERS.ID, COUNT(*) AS number_classes FROM TEACHERS
JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID
JOIN PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
GROUP BY TEACHERS.ID)
SELECT FIRST_NAME, LAST_NAME FROM TEACHERS
JOIN teacher_workload
ON teacher_workload.ID = TEACHERS.ID
WHERE teacher_workload.number_classes = 7;
``
Rachel Johnson
Rachel Johnson
UX Design Techdegree Graduate 39,649 Points

Yikes! I'm so grateful I didn't think to eliminate teachers with less than 7 periods a day, or I wouldn't gone on a trip like yours too!

3 Answers

Tommy Gebru
Tommy Gebru
29,403 Points

How would this be simplified using HAVING ?

Paul Brubaker
Paul Brubaker
5,873 Points

Having allows one to filter rows by some criteria based on the already grouped rows, in this case the value of the column COUNT(*) AS number_classes. So by using HAVING, my script shown above is simplified to something like what Ben showed in his solution. I felt a little silly for coming up with a complicated workaround when all that was needed was one simple keyword after watching the rest of the video.

SELECT TEACHERS.ID, COUNT(*) AS number_classes FROM TEACHERS
JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID
JOIN PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
GROUP BY TEACHERS.ID
HAVING number_classes = 7;
``
Tommy Gebru
Tommy Gebru
29,403 Points

Ah I see, also should there be a comma between the separate join statements?

Paul Brubaker
Paul Brubaker
5,873 Points

I believe in SQLite a comma is only used between two join statements if one opts to not use the join keyword each time. So this would work:

SELECT TEACHERS.ID, COUNT(*) AS number_classes FROM TEACHERS
JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID,
 PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
GROUP BY TEACHERS.ID
HAVING number_classes = 7;

But this would result in an error:

SELECT TEACHERS.ID, COUNT(*) AS number_classes FROM TEACHERS
JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID,
JOIN PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
GROUP BY TEACHERS.ID
HAVING number_classes = 7;

I don't know about the join syntax for other versions of SQL.