Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

SQL Reporting by Example

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Least Popular Subject

2:38 with Ben Deitch

Which subject is the least popular, and how many students are taking it?

This video doesn't have any notes.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    Great job. 0:00
    Next they'd like to figure out which subject is the least 0:01
    popular amongst the students. 0:04
    Can you find which subject is taken the least and how many students are taking it? 0:06
    To figure out which subject is the least popular let's start by just figuring out 0:12
    how many students are taking each subject. 0:16
    So, let's start by selecting everything from the SUBJECTS table. 0:20
    Then, let's join from the SUBJECTS table to the CLASSES table. 0:26
    So JOIN CLASSES ON SUBJECTS.ID = CLASSES.ID, 0:30
    or rather CLASSES.SUBJECT_ID. 0:36
    And then, to get the number of students, we'll need to join to the SCHEDULE table. 0:42
    So JOIN SCHEDULE ON CLASSES.ID 0:48
    = SCHEDULE.CLASS_ID. 0:54
    And let's run this to make sure we've got all the syntax right. 0:58
    And then from here, we can count the rows in each subject 1:01
    to figure out how many students are in that subject. 1:06
    So let's change this to be grouping by the subject, so 1:10
    let's GROUP BY SUBJECT_ID. 1:14
    And then up here let's select the SUBJECT_ID as well as the COUNT. 1:18
    And actually rather than selecting the SUBJECT_ID, let's select the SUBJECT_NAME. 1:25
    So that's gonna be SUBJECTS.NAME. 1:28
    And then if we look through this, 1:34
    it looks like the answer's going to be Puppetry down here with 58 students. 1:36
    So let's see how we can get this into one query that says Puppetry and 58. 1:43
    So starting with the data set we have here, it's pretty simple. 1:48
    We just want to take the minimum of this right column. 1:52
    So I'm going to alias this column as CT standing for count. 1:56
    And then I'm gonna use a common table expression again. 2:02
    So WITH SUBJECT_COUNTS AS. 2:05
    Let's put that down there, add our other parenthesis. 2:09
    And then let's tab this out. 2:13
    And then down here, let's select the NAME. 2:17
    And we're going to select the minimum of what's now the CT column. 2:22
    And we're going to select this all from the SUBJECT_COUNTS 2:26
    common table expression. 2:30
    And if we run this, Puppetry, 58, perfect. 2:33

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up