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 开发者_StackOverflow| 5 | 2 | 15
---------------------------------------------
what modifications do i have to do to my query to get those results?
Cheers
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
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
精彩评论