Databases SQL Reporting by Example Day 2: Advanced Selecting A Smelly Issue

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
25,091 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.

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"