MySQL: COUNT() from multiple tables

Question

I have 3 tables user, person and company.
the user_id is a foreign key in both person and company tables.
what I want to do is to count how many persons and companies are related to each user, so I did the following query:

select u.id , u.username, count(c.user_id) as count_company, count(p.user_id) as count_people
from user u left join company c on (u.id = c.user_id)
left join person p on (u.id = p.user_id)
group by u.id, u.username

but I'm getting wrong result!!
This is the result I get:

however, when I try to count only the companies related to each user using this query:

select u.id , u.username, count(c.staff_user_id) as count_company
from fos_user u left join company c on (u.id = c.staff_user_id)
group by u.id, u.username

I get this result:

what am I missing here ?

Paul Spiegel · Accepted Answer · 2019-01-31 15:49:54Z

3

If a user is related to multiple companies and/or multiple persons, then you always count the cross join of them - Which is then the number of companies multiplied with the number of persons.

To solve that problem (in your case) I would just use subqueries:

select u.id, u.username, 
    (select count(*) from company c where u.id = c.user_id) as count_company,
    (select count(*) from person p  where u.id = p.user_id) as count_people
from user u

Note that a double join like in your query is from relational point of view (in most cases) just wrong.

answered Jan 31, 2019 at 15:49

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2019-01-31 15:35:43Z

0

seems you need the distinct count

select u.id 
    , u.username
    , count(distinct c.user_id) as count_company
    , count(distinct p.user_id) as count_people
from user u 
left join company c on (u.id = c.user_id)
left join person p on (u.id = p.user_id)
group by u.id, u.username

count(col) count the not null values for col in relation ... count(distinct col) count the distinct value for col1

answered Jan 31, 2019 at 15:35

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

MySQL: COUNT() from multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related