CODEWITHSUNDEEP
mysqlcountgroup-by
bhttoan
bhttoan

Reputation: 2736

Count function returning wrong value when joined

I am trying to find a count of actions completed along with company details for companies who have more than 10 users.

The code I am using is:

SELECT 
    c.company_name,
    count(a.action) as actions,
    count(u.user_id) as users 
FROM 
    companies c 
LEFT JOIN 
    auditing a on c.id=a.company_id 
LEFT JOIN
    users u on c.id=u.company_id
GROUP BY 
    c.id 
HAVING 
    count(u.user_id)>10
LIMIT
    10

When I run this it displays an incorrect number of actions.

For example, for the first company in the table it returns:

Company 1,111026,111026

whereas it should return:

Company 1,5093,22

I need to get back the number of actions and the number of users for all companies - how can I do this?

Upvotes: 2

Views: 860

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

try putting in DISTINCT so that way you aren't counting duplicates

SELECT 
    c.company_name,
    count(DISTINCT a.action) as actions,
    count(DISTINCT u.user_id) as users 
FROM 
    companies c 
LEFT JOIN 
    auditing a on c.id=a.company_id 
LEFT JOIN
    users u on c.id=u.company_id
GROUP BY 
    c.id 
HAVING 
    count(u.user_id)>10
LIMIT
    10

Upvotes: 4

Related Questions