MySQL query to count occurrences from multiple tables

Question

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.

Try left join. it will consider all matching from the table on the left side. select persons.*, count(pens.person_id),count(chairs.person_id),count(books.person_id) from persons left join pens on pens.person_id=persons.id left join books on books.person_id=persons.id left join chairs on chairs.person_id=persons.id group by persons.id; — Rajen Raiyarela
– Rajen Raiyarela, Commented Dec 14, 2022 at 3:15
I already tried the left join options and the book counts turned out to be wrong, as described in my post, thank you though! — Kim
– Kim, Commented Dec 14, 2022 at 3:32

Tim Biegeleisen · Accepted Answer · 2022-12-14 03:39:29Z

1

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;

edited Dec 14, 2022 at 3:39

answered Dec 14, 2022 at 3:20

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kim Over a year ago

This one works! Just want to point out that ps.person needs to be ps.person_id (same with c.person_id and b.person_id) but it's not important as this is some fake example anyway. Thank you very much!

Collectives™ on Stack Overflow

MySQL query to count occurrences from multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related