Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

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

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
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Rachel Johnson
Treehouse Project Reviewer

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
30,152 Points

How would this be simplified using HAVING ?

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
30,152 Points

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

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.