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 Least Popular Subject

György Varga
György Varga
19,198 Points
Posted by György Varga
György Varga
19,198 Points

Can you please chech my solution?

Hi!

I did this:

SELECT subjects.name, COUNT(students.id) AS "Number of Students" FROM students
INNER JOIN schedule ON students.id = schedule.student_id
INNER JOIN classes ON schedule.class_id = classes.id
INNER JOIN subjects ON classes.subject_id = subjects.id
GROUP BY subjects.name
ORDER BY "Number of Students"
LIMIT 1;

But before limiting the results to 1 I've got different results than on the video. For example on math I've got 512 students. Can you please check it? Thank you!

2 Answers

Ben Deitch
STAFF
Ben Deitch
Treehouse Teacher
Ben Deitch
Ben Deitch
Treehouse Teacher

Hey György!

The difference is from grouping on SUBJECTS.NAME instead of SUBJECTS.ID. The SUBJECTS table uses a second GRADE column to distinguish between the 3 grade levels of a subject. So instead of seeing 6th, 7th, and 8th grade math as 3 separate subjects, they all end up just being 'Math'.

Mark Chesney
Mark Chesney
11,747 Points

Yes, in agreement with what Mr. Ben said, but with a minor tweak: I believe he had meant to say the difference between GROUP BY SUBJECTS.ID vs GROUP BY CLASS_ID -- the latter would result in 13 students in the smallest of the Puppetry classes, which is answering a slightly different question.

To clarify, there is no difference among the following three options:

... GROUP BY SUBJECTS.NAME
... GROUP BY SUBJECTS.ID
... GROUP BY SUBJECT_ID

Ben, thank you for this video clarification -- I had originally run a GROUP BY CLASS_ID! :)

Gavin Schilling
Gavin Schilling
37,904 Points
Gavin Schilling
Gavin Schilling
37,904 Points

Here is a cleaner version but follows more or less the flow of the video:

WITH COMMON_EXPRESSION AS (
  SELECT DISTINCT(subjects.name) AS "Least Attended Class", COUNT(schedule.class_id) AS "Student Enrollment" FROM SUBJECTS
  JOIN CLASSES ON subjects.id = classes.subject_id
  JOIN SCHEDULE ON classes.id = schedule.class_id
  GROUP BY classes.subject_id
  )

SELECT "Least Attended Class", MIN("Student Enrollment") AS "Students Currently Enrolled" FROM COMMON_EXPRESSION;

Hope that helps!