henryzo henryzo - 7 months ago 10
SQL Question

using count to count values and nulls

I have the query below


List the number of students graduated in 2009 by department. The results should show 0 for the departments that do not have any student graduated in 2009.


im having trouble with the 2nd part of the question. as of right now my query only shows the department that have students that graduated. i have no idea how to make the table show the departments that dd not have any students graduate.

my query looks like this

select d.name, count(s.major_id) as students from departments d
right join students s on s.major_id = d.id where extract( year from s.graduation_date ) = 2009
group by d.name

Answer

Try with a LEFT JOIN instead of a RIGHT JOIN:

select d.name, count(s.major_id) as students 
from departments d
left join students s on s.major_id = d.id and 
                        extract( year from s.graduation_date ) = 2009
group by d.name

Note that extract( year from s.graduation_date ) = 2009 predicate should be placed in the ON clause, otherwise LEFT JOIN becomes an INNER JOIN.

Output:

 name            | students
=================+============
Computer Science |  0
Drama            |  1
Math             |  2