Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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 upRelated 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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up