**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?

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