I have a query involving three tables. The relevant fields are listed here: users(id, role_id, geo_id, first_name, last_name), cities(city_name), and a junction table coordinators_clients(coordinator_id, client_id) where there is a many-to-many relationship. A user who is a coordinator will have role_id = 2.
I would like to list all the coordinators with their cities, and the number of clients that they have.
So far, my query is:
SELECT users.id, CONCAT(' ', users.first_name, users.last_name) AS 'Coordinator', cities.city_name AS 'City' FROM users JOIN cities ON cities.id = users.geo_id WHERE users.role_id = 2 ORDER BY cities.city_name ASC, users.last_name ASC, users.first_name ASC;
I am having problems adding a column showing the total number of clients that each coordinator has. I have this -
COUNT(coordinators_clients.client_id) AS 'Number of Clients' GROUP BY coordinators_clients.coordinator_id
How do I integrate that into the main query? Everything I have tried so far has either resulted in an error, or a table with duplicate entries of coordinators.