CODEWITHSUNDEEP
mysql
n3ISe
n3ISe

Reputation: 159

MySQL how to count from GROUP BY

date    | userid | companyid  
12.8.14 |   1    |    1  
12.8.14 |   2    |    2  
12.8.14 |   3    |    1

I had a table like above. It is easy to count how many company 1 from table with normal query.

My question is : if my query is select * from table where companyid = '1' group by date, how can i get mysql_num_row equal to 2 for company 1 and userid is 1 and 3?

select * from table where companyid = '1' group by date will only return me mysql_num_row equal 1 and result 12.8.14 | 1 | 1

Upvotes: 0

Views: 48

Answers (3)

sbrbot
sbrbot

Reputation: 6469

You wanted:

select date,companyid,count(*)
from table
where userid = 1
group by date,companyid

Upvotes: 0

Sathish
Sathish

Reputation: 4487

try like this also

select *,(select count(*) from table1 where companyid=a.companyid) as count 
from t  as a where companyid=1

Upvotes: 0

Lloyd Banks
Lloyd Banks

Reputation: 36699

You can nest a query to get the sum of company one entries and then join that nested query to an outer query:

SELECT ABB2.*, ABB1.mysql_num_row
FROM `table` AS ABB2
  JOIN 
      (SELECT companyid, COUNT(userid) AS mysql_num_row
      FROM `table`
      GROUP BY companyid) AS ABB1 ON ABB1.companyid = ABB2.companyid
WHERE ABB2.companyid = 1;

Example

Upvotes: 1

Related Questions