MySQL COUNT with GROUP BY and zero entries

I have 2 tables:

Table 1. options_ethnicity with the following entries:

ethnicity_id ethnicity_name  
1 White  
2 Hispanic  
3 African/American  

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

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

White 3  
Hispanic 2

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

Any help would be appreciated.

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.ethnici开发者_开发问答ty_id 

UNION ALL

SELECT 
    'NULL Placeholder' AS ethnicity_name, 
    COUNT(inquiries.inquiry_id) AS count 
FROM inquiries 
WHERE inquiries.ethnicity_id IS 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

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.

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

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 |  ?

You counted a string instead of the right column

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

Why don't you "reverse" your query?

SELECT 
  options_ethnicity.ethnicity_name, 
  COUNT(inquiries.ethnicity_id) AS count
FROM 
  options_ethnicity
  Left Join inquiries On options_ethnicity.ethnicity_id = inquiries.ethnicity_id
GROUP BY 
  options_ethnicity.ethnicity_id

You still might need a Coalesce call, but to me, this query makes more sense for what you're trying to accomplish.