在MySQL中使用GROUP BY和SUM保持零值 [英] Keep zero values with GROUP BY and SUM in mysql

问题描述

我有两个表career_types和bookings

I have two tables career_types and bookings

career_types

career_types

id | name
1 | foo
2 | bar
3 | baz

预订

id | career_type_id | details
1 | 1 | foo
2 | 1 | bar
3 | 2 | baz

我的预订表中有很多关于所有职业类型的条目，但career_types.id为3除外.

My bookings table has a lot of entries for all career types except career_types.id of 3.

我想要一个查询，该查询将列出我的所有career_types.name以及每次预订的数量，如果career_types.id = 1，则该列表中没有预订.我想返回0.

I want a query that will list all my career_types.name's and how many each booking has, and in the case of career_types.id = 1 where there are no bookings for it. I want to return 0.

到目前为止，我一直在尝试

So far I've been trying variations of

SELECT career_types.name, sum(bookings.id)
FROM career_types
LEFT JOIN bookings ON (career_types.id = bookings.career_type_id)
GROUP BY career_types.id

我正在寻找的结果是

career_types.name | sum
foo | 2
bar | 1
baz | 0

但是目前我无法获得baz的任何输出.

But at the moment I'm not able to get any output for baz.

推荐答案

聚合函数在其计算中仅包含非null值.对于SUM和大多数聚合函数，如果未遇到任何非null值，则结果为null.如果需要零而不是零，则可以使用以下方法简单解决: IFNULL(SUM(x)，0)

Aggregate functions only include non-null values in their calculations. In the case of SUM, and most aggregate functions, the result is null if no non-null values are encountered. If zero is needed, rather than null, this can be accounted for simply with: IFNULL(SUM(x), 0)

这篇关于在MySQL中使用GROUP BY和SUM保持零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！