Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

Posted by Paul Brubaker
Paul Brubaker
14,290 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 KTY Johnson
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Rachel KTY Johnson
Treehouse Project Reviewer
Rachel KTY Johnson
.a{fill-rule:evenodd;}techdegree seal-36
Rachel KTY 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,164 Points
Tommy Gebru
Tommy Gebru
30,164 Points

How would this be simplified using HAVING ?

Paul Brubaker
Paul Brubaker
14,290 Points
Paul Brubaker
Paul Brubaker
14,290 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
30,164 Points
Tommy Gebru
Tommy Gebru
30,164 Points

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

Paul Brubaker
Paul Brubaker
14,290 Points
Paul Brubaker
Paul Brubaker
14,290 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.