Mysql multi count () in one query

Question

I am trying to count several joined tables, but without luck I get the same numbers for each column (tUsers,tLists,tItems) . My request:

 select COUNT(users.*) as tUsers, COUNT(lists.*) as tLists, COUNT(items.*) as tItems, companyName from users as c join lists as l on c.userID = l.userID join items as i on c.userID = i.userID group by companyID 

As a result, I want to get

 --------------------------------------------- # | CompanyName | tUsers | tlists | tItems 1 | RealCoName | 5 | 2 | 15 --------------------------------------------- 

What changes do I need to make for my request to get these results?

Greetings

Answer 1

try it

 SELECT u.userID, companyName, Count(DISTINCT l.listid) as tLists, Count(DISTINCT i.items) as tItems FROM users u LEFT JOIN lists l ON u.userID=l.userID LEFT JOIN items i ON u.userID=i.userID GROUP BY u.companyID 

Answer 2

You can do this using a helper query

 select (select count(*) from users where userID=YourUserID) tUsers, (select count(*) from lists where userID=YourUserID) as tLists, (select count(*) from items where userID=YourUserID) as tItems, companyName from company group by companyID