MySQL query to count occurrences from multiple tables

December 14, 2022

I have a problem when I have to select everything from one table (persons) then count how many objects they own by counting their occurrences on other tables (pens, chairs, books)

The current data is as followed:

select * from persons;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  2 | Brad  |
|  3 | Cathy |
+----+-------+
select * from pens;
+----+-----------+
| id | person_id |
+----+-----------+
|  1 | 2         |
|  2 | 2         |
|  3 | 2         |
|  4 | 3         |
+----+-----------+
select * from chairs;
+----+-----------+
| id | person_id |
+----+-----------+
|  1 | 1         |
+----+-----------+
select * from books;
+----+-----------+
| id | person_id |
+----+-----------+
|  1 | 1         |
|  2 | 2         |
|  3 | 3         |
+----+-----------+

I want the result to be something like this

+----+-------+-----------------------+-------------------------+------------------------+
| id | name  | count(pens.person_id) | count(chairs.person_id) | count(books.person_id) |
+----+-------+-----------------------+-------------------------+------------------------+
|  1 | Alex  |                     0 |                       1 |                      1 |
|  2 | Brad  |                     3 |                       0 |                      1 |
|  3 | Cathy |                     1 |                       0 |                      1 |
+----+-------+-----------------------+-------------------------+------------------------+

I have tried using inner join and left outer join, but join gave me an empty set (since no person matches all of the objects) and left outer join gave me incorrect results:

> select persons.*, count(pens.person_id),count(chairs.person_id),count(books.person_id) from persons join pens on pens.person_id=persons.id join books on books.person_id=persons.id join chairs on chairs.person_id=persons.id group by persons.id;
Empty set (0.002 sec)

> select persons.*, count(pens.person_id),count(chairs.person_id),count(books.person_id) from persons left outer join pens on pens.person_id=persons.id left outer join books on books.person_id=persons.id left outer join chairs on chairs.person_id=persons.id group by persons.id;
# +----+-------+-----------------------+-------------------------+------------------------+
id | name  | count(pens.person_id) | count(chairs.person_id) | count(books.person_id) |
# +----+-------+-----------------------+-------------------------+------------------------+
1 | Alex  |                     0 |                       1 |                      1 |
2 | Brad  |                     3 |                       0 |                      3 |
3 | Cathy |                     1 |                       0 |                      1 |
# +----+-------+-----------------------+-------------------------+------------------------+

Any suggestions will be greatly appreciated, sorry if it’s obvious, I’m fairly new at this.

>Solution :

Using a left join approach to subqueries on each table we can try:

SELECT
    p.id,
    p.name,
    COALESCE(ps.cnt, 0) AS cnt_pens,
    COALESCE(c.cnt, 0) AS cnt_chairs,
    COALESCE(b.cnt, 0) AS cnt_books
FROM persons p
LEFT JOIN
(
    SELECT person_id, COUNT(*) AS cnt
    FROM pens
    GROUP BY person_id
) ps
    ON ps.person_id = p.id
LEFT JOIN
(
    SELECT person_id, COUNT(*) AS cnt
    FROM chairs
    GROUP BY person_id
) c
    ON c.person_id = p.id
LEFT JOIN
(
    SELECT person_id, COUNT(*) AS cnt
    FROM books
    GROUP BY person_id
) b
    ON b.person_id = p.id
ORDER BY
    p.id;