Q: Returning count as an additional column I'm sort of new to MySQL but I don't quite know how to accomplish this: Basically, I have two tables: One with companies and one with locations. The locations table has a foreign key to companies so that a company can have multiple locations. I have a simple select query that returns the company id and location id, as such:
SELECT location.id as location_id, company.id as company_id FROM company 
INNER JOIN location ON (location.company_id = company.id);

I would like to know if there is a way to also return the number of locations as an extra column. It would return something like:
number_of_locations |  company_id | location_id
2 | 1 | 1
2 | 1 | 2
1 | 2 | 3
2 | 4 | 5
2 | 4 | 6

Thanks!

A: Just use window functions:
select t.*, count(*) over (partition by company_id) as number_of_locations
from t;


A: Here is the SQL for your intended output -
Solution# 1 -
SELECT count(location.id) over (partition by location.company_id) as number_of_locations,
company.id as company_id,
location.id as location_id
FROM company 
INNER JOIN location ON (location.company_id = company.id);

Solution# 2 -
SELECT cl.number_of_locations,
company.id as company_id,
location.id as location_id
FROM company 
INNER JOIN location ON location.company_id = company.id
INNER JOIN (SELECT count(location.id) as number_of_locations,
company.id as company_id
FROM company 
INNER JOIN location ON location.company_id = company.id GROUP BY company.id) cl
ON company.id = cl.company_id;

