using count to count values and nulls

Question

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

and my table looks like this

name    students
Math       2
Drama      1

how can i get it to show the other departments with no students graduating?

database is

create table departments (
    id      integer primary key,
    name    varchar(255)
);

insert into departments (id, name) values (10, 'Computer Science');
insert into departments (id, name) values (20, 'Math');
insert into departments (id, name) values (30, 'Drama');

create table faculty (
    id              integer primary key,
    name            varchar(255),
    department_id   integer references departments(id)
);

insert into faculty (id, name, department_id) values (1, 'Turing', 10);
insert into faculty (id, name, department_id) values (2, 'Newton', 20);
insert into faculty (id, name, department_id) values (3, 'Einstein', 20);
insert into faculty (id, name, department_id) values (4, 'Brando', 30);
insert into faculty (id, name, department_id) values (5, 'Joe', 30);
insert into faculty (id, name, department_id) values (6, 'Gray', 10);

create table students (
    id              integer primary key,
    name            varchar(255),
    graduation_date date,
    major_id        integer references departments(id)
);

insert into students (id, name, graduation_date, major_id) values
    (1, 'Joe', null, 10);
insert into students (id, name, graduation_date, major_id) values
    (2, 'Amy', '2009-04-22', 20);
insert into students (id, name, graduation_date, major_id) values
    (3, 'Max', null, 10);
insert into students (id, name, graduation_date, major_id) values
    (4, 'Sue', '2009-01-10', 20);
insert into students (id, name, graduation_date, major_id) values
    (5, 'Bob', '2009-03-05', 30);
insert into students (id, name, graduation_date, major_id) values
    (6, 'Kim', null, 20);
insert into students (id, name, graduation_date, major_id) values
    (7, 'Art', null, 30);
insert into students (id, name, graduation_date, major_id) values
    (8, 'Pat', '2005-07-11', 20);
insert into students (id, name, graduation_date, major_id) values
    (9, 'Lee', null, 10);

Please do not vandalize your post.

Andras Deak -- Слава Україні
– Andras Deak -- Слава Україні

2016-04-28 00:35:46 +00:00
Commented Apr 28, 2016 at 0:35 — Andras Deak -- Слава Україні
– Andras Deak -- Слава Україні, Commented Apr 28, 2016 at 0:35

Giorgos Betsos · Accepted Answer · 2016-04-27 08:52:11Z

1

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

answered Apr 27, 2016 at 8:52

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

henryzo Over a year ago

i swear i thought i used every join possible lol , thank you for the help. saved me a long night. and thanks for the tip on the extrac predicate, i had no idea about that.

Collectives™ on Stack Overflow

using count to count values and nulls

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related