Lets say i have 2 tables
Companies
company_id
name
Users
id
company_id
name
each company has multiple users assign to it... which is referenced in the company_id field from each record in the users table
HOW can i get a record showing the (company_id), (company_name) and (number or users)
for eg:
id# 1234 | name# Microsoft | n of users# 2000
I dont know how to make this query, i know i have to use the function COUNT() but i dont know how
If you want to get all companies even if they don't have any users yet use OUTER JOIN
SELECT c.company_id, c.name company_name, COUNT(u.id) no_of_users
FROM companies c LEFT JOIN users u
ON c.company_id = u.company_id
GROUP BY c.company_id, c.name
Sample output:
| COMPANY_ID | COMPANY_NAME | NO_OF_USERS | |------------|--------------|-------------| | 1 | Company1 | 3 | | 2 | Company2 | 2 | | 3 | Company3 | 0 |
Here is SQLFiddle demo
this will be the query
select Companies.company_id,Companies.name,count(Users .id) from Companies,Users where Companies=company_id and Users =company_id group by company_id
Try :
SELECT companies.company_id,companies.company_name,COUNT(users.id)
FROM companies, users
WHERE companies.id = users.company_id
group by companies.id
