MySQL select count with join

Question

I am trying to get a list of data from multiple tables in a view for analytics - most of the data comes from one table and then I need to get the counts from multiple other tables to show number of logins, number of users and so on.

I am using the below query (simplified to show only some rows and one join):

Select 
    companies.company_name, companies.last_login, companies.last_ip,
    (SELECT COUNT(*) FROM auditing_master GROUP BY company_id) AS audit_count
From
    companies
Left Join
    auditing_master On auditing_master.company_id = companies.id 
Group by 
    companies.id

When I run this I get an error:

#2014 - Commands out of sync; you can't run this command now

If I run it without the GROUP BY in the SELECT COUNT (*) then it doesn't give any error but it returns a count of all entries in the auditing_master table regardless of which company it refers to.

How can I get a single query to show me the required data from the companies table whilst also showing a total from auditing master (and others) grouped by the company ID?

Edit

Code when using multiple count/joins:

Select 
    c.company_name, c.last_login, c.last_ip,
    COUNT(am.company_id) AS audit_count,
    COUNT(u.company_id) AS users,
    COUNT(e.company_id) AS employees
From
    companies c
Left Join
    auditing_master am On am.company_id = c.id 
Left Join
    users u On u.company_id = c.id 
Left Join
    employees e On e.company_id = c.id 
Group by 
    c.id

This query locally in PHPMyAdmin on WAMP with only around 10 companies takes 7 seconds to complete and give "meanngless" results that don't correlate to anything.

Thanks

aksu · Accepted Answer · 2014-12-15 18:18:47Z

2

Inner queries in the SELECT clause must return a SINGLE VALUE RESULT.

Something similar to that, should bring what u need.

Select 
    companies.company_name, companies.last_login, companies.last_ip,
    COUNT(*) AS audit_count  
From  
    companies  
Left Join  
    auditing_master On auditing_master.company_id = companies.id   
Group by   
    companies.id

edited Dec 15, 2014 at 18:18

aksu

5,2365 gold badges26 silver badges39 bronze badges

answered Oct 21, 2013 at 14:09

Itay Moav -Malimovka

53.8k68 gold badges198 silver badges292 bronze badges

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

Comments

JoDev · Accepted Answer · 2013-10-21 14:33:39Z

1

Did you try this? (No need a subquery because no where clause into it, and LEFT JOIN already add auditing_master rows grouped by company_id = companies.id)

Select 
    companies.company_name, companies.last_login, companies.last_ip,
    COUNT(company_id) AS audit_count
From
    companies
Left Join
    auditing_master On auditing_master.company_id = companies.id 
Group by 
    companies.id

EDIT

Maybe if you add IF statement to avoid COUNT when there is no auditing_master for a given company.

SELECT
    companies.company_name, companies.last_login, companies.last_ip,
    IFNULL(am.company_id,0,COUNT(am.company_id)) AS audit_count
FROM
    companies
INNER JOIN
    auditing_master am ON auditing_master.company_id = companies.id 
GROUP BY
    companies.id

Feel free to put the entire SQL... Because the problem can be somewhere else!

edited Oct 21, 2013 at 14:33

answered Oct 21, 2013 at 14:07

JoDev

6,9231 gold badge25 silver badges37 bronze badges

1 Comment

bhttoan Over a year ago

I had that originally but it doesn't work when there are more than one count/join - just adding multiple counts gives ambiguous column name company_id and if I try and add auditing_master.company_id etc to the COUNT then it appears to freeze as it timesout in PHPMyAdmin at showing loading on the screen and never completes

Collectives™ on Stack Overflow

MySQL select count with join

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related