使用count来计算值和空值

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.

列出2009年按部门分列的学生人数。对于2009年没有学生毕业的部门,结果应显示为0。

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

1 个解决方案

#1

Try with a LEFT JOIN instead of a RIGHT JOIN:

尝试使用LEFT JOIN而不是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.

请注意,提取(来自s.graduation_date的年份)= 2009谓词应放在ON子句中,否则LEFT JOIN将成为INNER JOIN。

Output:

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

#1