mysql multi count() in one query

Question

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

Shakti Singh · Accepted Answer · 2010-12-31 07:44:56Z

5

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

edited Dec 31, 2010 at 7:44

answered Dec 31, 2010 at 7:33

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

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

2 Comments

afarazit Over a year ago

Hi Shakti Singh, that query gives me the results I wanted. I just added "Count(DISTINCT u.userID) as tUsers". I didn't knew about the existence of DISTINCT, thank you for that.

acedanger Over a year ago

This worked for something I'm working on as well. My only question is why is the distinct needed for each count column?

shankhan · Accepted Answer · 2010-12-31 07:15:18Z

3

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

answered Dec 31, 2010 at 7:15

shankhan

6,5812 gold badges22 silver badges22 bronze badges

2 Comments

afarazit Over a year ago

hi shankhan, i won't insert "YourUserID" using an application, "YourUserID" is coming from the JOINs. Can you please modify your query so it does that?

shankhan Over a year ago

hmm in that case you will have to write join in each sub query and it will be little costly

Collectives™ on Stack Overflow

mysql multi count() in one query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related