SQL 如何在 COUNT 聚合中包含“零”/“0”结果？

How to include "zero" / "0" results in COUNT aggregate?

提问by BrownE

I've just got myself a little bit stuck with some SQL. I don't think I can phrase the question brilliantly - so let me show you.

我只是让自己有点被一些 SQL 困住了。我不认为我可以巧妙地表达这个问题 - 所以让我告诉你。

I have two tables, one called person, one called appointment. I'm trying to return the number of appointments a person has (including if they have zero). Appointment contains the person_idand there is a person_idper appointment. So COUNT(person_id)is a sensible approach.

我有两张桌子，一张叫人，一张叫约会。我正在尝试返回一个人的约会次数（包括他们是否为零）。约会包含person_id并且person_id每个约会都有一个。COUNT(person_id)明智的做法也是如此。

The query:

查询：

SELECT person_id, COUNT(person_id) AS "number_of_appointments" 
FROM appointment 
GROUP BY person_id;

Will return correctly, the number of appointments a person_id has. However, a person who has 0 appointments isn't returned (obviously as they are not in that table).

将正确返回，person_id 的约会次数。但是，有 0 个约会的人不会返回（显然因为他们不在该表中）。

Tweaking the statement to take person_id from the person table gives me something like:

调整语句以从 person 表中获取 person_id 给了我类似的东西：

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM appointment
JOIN person ON person.person_id = appointment.person_id
GROUP BY person.person_id;

This however, will still only return a person_id who has an appointment and not what I want which is a return with persons who have 0 appointments!

然而，这仍然只会返回一个有约会的 person_id 而不是我想要的，这是一个有 0 个约会的人的回报！

Any suggestions please?

请问有什么建议吗？

采纳答案by a_horse_with_no_name

You want an outer join for this (and you need to use person as the "driving" table)

您需要一个外部连接（并且您需要使用 person 作为“驱动”表）

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULLfor those persons that do not have an appointment. The aggregate function count()will not count NULLvalues and thus you'll get a zero.

这样做的原因是外部（左）连接将返回NULL给那些没有预约的人。聚合函数count()不会计算NULL值，因此您将得到零。

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

如果您想了解有关外连接的更多信息，这里有一个不错的教程：http: //sqlzoo.net/wiki/Using_Null

回答by Hamlet Hakobyan

You must use LEFT JOINinstead of INNER JOIN

您必须使用LEFT JOIN代替INNER JOIN

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

回答by Francois Mazet

if you do the outer join (with the count), and then use this result as a sub-table, you can get 0 as expected (thanks to the nvl function)

如果你做外连接（用计数），然后用这个结果作为子表，你可以按预期得到 0（感谢 nvl 函数）

Ex:

前任：

select P.person_id, nvl(A.nb_apptmts, 0) from 
(SELECT person.person_id
FROM person) P
LEFT JOIN 
(select person_id, count(*) as nb_apptmts
from appointment 
group by person_id) A
ON P.person_id = A.person_id

回答by Rohini C.G

USE join to get 0 count in the result using GROUP BY.

USE join 使用 GROUP BY 在结果中获得 0 计数。

simply 'join' does Inner join in MS SQL so , Go for left or right join.

简单地“加入”在 MS SQL 中进行内部联接，因此，选择左联接或右联接。

If the table which contains the primary key is mentioned first in the QUERY then use LEFT join else RIGHT join.

如果在 QUERY 中首先提到包含主键的表，则使用 LEFT join else RIGHT join。

EG:

例如：

select WARDNO,count(WARDCODE) from MAIPADH 
right join  MSWARDH on MSWARDH.WARDNO= MAIPADH.WARDCODE
group by WARDNO

.

.

select WARDNO,count(WARDCODE) from MSWARDH
left join  MAIPADH on MSWARDH.WARDNO= MAIPADH.WARDCODE group by WARDNO

Take group by from the table which has Primary key and count from the another table which has actual entries/details.

从具有主键的表中进行分组，并从具有实际条目/详细信息的另一个表中进行计数。

回答by sfj

To change even less on your original query, you can turn your join into a RIGHTjoin

要对原始查询进行更少的更改，您可以将连接转换为RIGHT连接

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM appointment
RIGHT JOIN person ON person.person_id = appointment.person_id
GROUP BY person.person_id;

This just builds on the selected answer, but as the outer join is in the RIGHTdirection, only one word needs to be added and less changes. - Just remember that it's there and can sometimes make queries more readable and require less rebuilding.

这只是建立在所选答案的基础上，但由于外连接是在RIGHT方向上，因此只需要添加一个单词并且更改较少。- 请记住它就在那里，有时可以使查询更具可读性并且需要更少的重建。

回答by dannyw

The problem with a LEFT JOIN is that if there are no appointments, it will still return one row with a null, which when aggregated by COUNT will become 1, and it will appear that the person has one appointment when actually they have none. I think this will give the correct results:

LEFT JOIN 的问题是，如果没有约会，它仍然会返回一行空值，当被 COUNT 聚合时将变为 1，并且看起来这个人有一个约会，而实际上他们没有约会。我认为这将给出正确的结果：

SELECT person.person_id,
(SELECT COUNT(*) FROM appointment WHERE person.person_id = appointment.person_id) AS 'Appointments'
FROM person;