MySQL COUNT，具有GROUP BY和零项 [英] MySQL COUNT with GROUP BY and zero entries

问题描述

我有2张桌子:

表1. options_ethnicity，其中包含以下条目:

Table 1. options_ethnicity with the following entries:

ethnicity_id ethnicity_name  
1 White  
2 Hispanic  
3 African/American  

表2. inquiries，其中包含以下条目:

Table 2. inquiries with the following entries:

inquiry_id ethnicity_id  
1 1  
2 1  
3 1  
4 2  
5 2  

我想生成一个表格，按种族显示查询数量.到目前为止，我的查询看起来像这样:

I want to generate a table that shows the number of inquires by ethnicity. My query so far looks like this:

SELECT options_ethnicity.ethnicity_name, COUNT('inquiries.ethnicity_id') AS count
FROM (inquiries 
    LEFT JOIN options_ethnicity ON
    options_ethnicity.ethnicity_id = inquiries.ethnicity_id)  
GROUP BY options_ethnicity.ethnicity_id

查询给出正确的答案，但没有针对非洲/美洲的列，其结果为0.

The query gives the correct answer but there is no column for African/American which has 0 results.

White 3  
Hispanic 2

如果我用RIGHT JOIN代替LEFT JOIN，我会得到所有3个种族的名字，但是非洲裔/美洲裔的计数是错误的.

If I replace the LEFT JOIN with a RIGHT JOIN, I get all 3 ethnicity names, but the count for African/American is wrong.

White 3  
Hispanic 2  
African/American 1

任何帮助将不胜感激.

这是此帖子的更新，其中的内容似乎可以正常使用:

Here's an update to this post with what seems to be a working query:

SELECT 
    options_ethnicity.ethnicity_name, 
    COALESCE(COUNT(inquiries.ethnicity_id), 0) AS count 
FROM options_ethnicity LEFT JOIN inquiries ON inquiries.ethnicity_id = options_ethnicity.ethnicity_id 
GROUP BY options_ethnicity.ethnicity_id 

UNION ALL

SELECT 
    'NULL Placeholder' AS ethnicity_name, 
    COUNT(inquiries.inquiry_id) AS count 
FROM inquiries 
WHERE inquiries.ethnicity_id IS NULL 

推荐答案

由于您使用的是LEFT JOIN，因此对LEFT JOIN中定义的表的引用可以为null.这意味着您需要将此NULL值转换为零(在这种情况下):

Because you're using a LEFT JOIN, references to the table defined in the LEFT JOIN can be null. Which means you need to convert this NULL value to zero (in this case):

   SELECT oe.ethnicity_name, 
          COALESCE(COUNT(i.ethnicity_id), 0) AS count
     FROM OPTIONS_ETHNICITY oe
LEFT JOIN INQUIRIES i ON i.ethnicity_id = oe.ethnicity_id
 GROUP BY oe.ethnicity_id

此示例使用 COALESCE ， ANSI标准的处理NULL值的方法.它将返回第一个非null值，但是如果找不到，则将返回null. IFNULL 是有效的替代方法MySQL，但在COALESCE时不能移植到其他数据库.

This example uses COALESCE, an ANSI standard means of handling NULL values. It will return the first non-null value, but if none can be found it will return null. IFNULL is a valid alternative on MySQL, but it is not portable to other databases while COALESCE is.

在实际数据库表中，查询表中有一些条目，其中ethnicity_id为NULL，即未记录种族.关于如何获取这些空值以进行显示的任何想法吗?

In the real database table, there are some entries in the inquires table where the ethnicity_id is NULL, i.e. the ethnicity was not recorded. Any idea on how to get these null values to be counted so that they can be shown?

我想我了解您面临的问题:

I think I understand the issue you're facing:

   SELECT oe.ethnicity_name, 
          COALESCE(COUNT(i.ethnicity_id), 0) AS count
     FROM (SELECT t.ethnicity_name,
                  t.ethnicity_id
             FROM OPTIONS_ETHNICITY t
           UNION ALL
           SELECT 'NULL placeholder' AS ethnicity_name,
                  NULL AS ethnicity_id) oe
LEFT JOIN INQUIRIES i ON i.ethnicity_id = oe.ethnicity_id
 GROUP BY oe.ethnicity_id

这将拾取所有NULL ethncity_id实例，但会将计数归因于"NULL占位符"组. IE浏览器:

This will pickup all the NULL ethncity_id instances, but it will attribute the counting to the "NULL placeholder" group. IE:

ethnicity_name   |  COUNT
------------------------
White            |  3  
Hispanic         |  2
NULL placeholder |  ?

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