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 2: Advanced Selecting A Smelly Issue

Posted by Mark Chesney
Mark Chesney
11,747 Points

smelly issue: query came close

I'm curious: I came close with this query, but no cigar :| Can anyone figure out why? (I can't)

WITH class_sizes AS (
  SELECT CLASSES.PERIOD_ID, SCHEDULE.CLASS_ID, COUNT(CLASS_ID) AS "Students" FROM CLASSES 
  INNER JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID GROUP BY CLASS_ID
)
SELECT CLASSES.SUBJECT_ID, CLASSES.PERIOD_ID FROM CLASSES
JOIN class_sizes ON CLASSES.ID = class_sizes.CLASS_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE CLASSES.PERIOD_ID = 1 AND SUBJECTS.NAME = "Physical Education"

My answer is off: it would be 25+31 = 56

2 Answers

Daniel Phillips
Daniel Phillips
26,940 Points
Daniel Phillips
Daniel Phillips
26,940 Points

You're query works great! You're looking at the wrong column! The numbers 25 and 31 are the SUBJECT_IDs of the classes! If you look at the Students column from your CTE, you get 19+28=47.

Mark Chesney
Mark Chesney
11,747 Points
Mark Chesney
Mark Chesney
11,747 Points

oh, I've overlooked that -- thanks Daniel for pointing that out! I'll just add class_sizes.Students to make my new query this:

-- How many students have Physical Education during first period?
WITH class_sizes AS (
  SELECT CLASSES.PERIOD_ID, SCHEDULE.CLASS_ID, COUNT(CLASS_ID) AS "Students" FROM CLASSES 
  INNER JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID GROUP BY CLASS_ID
)
SELECT class_sizes.Students, CLASSES.SUBJECT_ID, CLASSES.PERIOD_ID FROM CLASSES
JOIN class_sizes ON CLASSES.ID = class_sizes.CLASS_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE CLASSES.PERIOD_ID = 1 AND SUBJECTS.NAME = "Physical Education"