CODEWITHSUNDEEP
mysql
henryzo
henryzo

Reputation: 105

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

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);

Upvotes: 1

Views: 47

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 1

Related Questions