CODEWITHSUNDEEP
mysqlcount
afarazit
afarazit

Reputation: 4984

mysql multi count() in one query

I'm trying to count several joined tables but without any luck, what I get is the same numbers for every column (tUsers,tLists,tItems). My query is:

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

The result I want to get is 

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

what modifications do i have to do to my query to get those results?

Cheers

Upvotes: 4

Views: 2884

Answers (2)

Shakti Singh
Shakti Singh

Reputation: 86406

Try this

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

Upvotes: 5

shankhan
shankhan

Reputation: 6571

You can do it by using sub 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

Upvotes: 3

Related Questions