CODEWITHSUNDEEP
mysql
Vlada Katlinskaya
Vlada Katlinskaya

Reputation: 1033

How can I build MySQL query with additional column equals to number of associated rows?

Let's say I have two tables Cities and Employees. Each employee associated with one city. How can I build a query resulting with all cities and will have additional column equals to number of employees associated with this city?

I tried the following:

SELECT *,COUNT(SELECT * FROM `Employees` WHERE `city_id` = `id`) AS `count` FROM `cities`

But it's not working.

Actually I have no idea where I should looking for. Hope that I will have any hint here.

Upvotes: 0

Views: 28

Answers (3)

Gervs
Gervs

Reputation: 1397

The predicate is: Give me all cities, and the number of employees belonging to them.

SELECT c.name, COUNT(e.id) cnt
FROM cities c
LEFT JOIN employees e
    ON c.id = e.city_id
GROUP BY c.name

This will give you all cities with cnt zero if a city has no employees belonging to it

Upvotes: 0

geoandri
geoandri

Reputation: 2428

You may try the following query using inner join between cities and employees table

  SELECT cities.* ,COUNT( `Employees`.`id`)   FROM `cities`
  INNER JOIN `Employees` on `Employees`.`city_id` = `cities`.`id`
  GROUP BY `cities`.`id`

Upvotes: 1

K-Jtan
K-Jtan

Reputation: 64

If you prefer old style SQL you could try something like this

SELECT cityTable.`name`, employeesTable.employees_count
FROM (SELECT `id`, `name` FROM `Cities`)citiesTable,
     (SELECT count(*) as employees_count, `city_id` FROM `Employees`)employeesTable
WHERE citiesTable.`id` = employeesTable.`city_id`

Upvotes: 0

Related Questions