Erdem Olcay Erdem Olcay - 3 months ago 8
MySQL Question

MySQL - Select number of occurrences of values from another table (including zeros)

I have two tables:


  • First table (names) contains different names, all names are included only once.

  • Second table (people) contains records of some people. All the names (person column) in this table are included in the name column of the first table.



names:

id name
--- -----
1 Linda
2 John
3 Mary
4 Charles


people:

id person
--- -----
1 John
2 Mary
3 Mary
4 Charles
5 Charles
6 Charles


SQL Fiddle: http://sqlfiddle.com/#!9/acaf4

I want to list the number of occurrences of name values (including zeros) in people table.

I used following query, but the result doesn't contain zero values:

SELECT person AS n,
COUNT(person) AS name_occurrence
FROM people
LEFT JOIN names ON people.person = names.name
GROUP BY person
ORDER BY name_occurrence ASC;


The result is:

n name_occurrence
------- ---------------
John 1
Mary 2
Charles 3


What I want is:

n name_occurrence
------- ---------------
Linda 0
John 1
Mary 2
Charles 3

Answer

Since the names table contains all known names, I think that you want to LEFT JOIN this table to a table containing the counts of each name as it appears in the people table. In the event that a name does not appear in people, I use COALESCE to replace that missing count with zero.

SELECT n.name,
       COALESCE(t.personCount, 0) AS personCount
FROM names n
LEFT JOIN
(
    SELECT person, COUNT(*) AS personCount
    FROM people
    GROUP BY person
) t
    ON n.name = t.person

Click the link below for a delightful Fiddle using the good work done by @strawberry :

SQLFiddle