Using GROUP BY with a junction table


#1

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.


#2

looks like a single coordinator may have multiple cities as well as multiple clients

gotta put one of those in a subquery

easy to make the choice which one it should be, since you're elaborating the cities but counting the clients

SELECT cities.city_name AS 'City'
     , users.id
     , CONCAT(' ', users.first_name, users.last_name) AS 'Coordinator'
     , sub.nmbr_clnts AS 'Number of Clients' 
  FROM users 
INNER
  JOIN cities 
    ON cities.id = users.geo_id 
INNER
  JOIN ( SELECT coordinator_id
              , COUNT(*) AS nmbr_clnts
           FROM coordinators_clients    
         GROUP 
             BY coordinator_id ) AS sub
    ON sub.coordinator_id = users.id         
 WHERE users.role_id = 2 
ORDER 
    BY cities.city_name ASC
     , users.last_name ASC
     , users.first_name ASC

#3

p.s. based on the variables in the function, i'm sure you meant to use CONCAT_WS instead of CONCAT


#4

There is a many-to-one relationship between coordinator and city. So a coordinator will only have one city.

As for CONCAT_WS, I had never heard of that, but it does seem to be the one to use. Thank you.

If I wanted to also include all the coordinators who didn't have any clients, I would change the INNER JOIN to a LEFT JOIN, right? (BTW, all coordinators are associated with a city).


#5

gaah, i should've seen that

SELECT cities.city_name AS 'City'
     , users.id
     , CONCAT_WS(' ', users.first_name, users.last_name) AS 'Coordinator'
     , COUNT(coordinators_clients.coordinator_id) AS 'Number of Clients' 
  FROM users 
INNER
  JOIN cities 
    ON cities.id = users.geo_id 
LEFT OUTER
  JOIN coordinators_clients
    ON coordinators_clients.coordinator_id = users.id 
 WHERE users.role_id = 2 
ORDER 
    BY cities.city_name ASC
     , users.last_name ASC
     , users.first_name ASC

#6

oh shoot, i forgot the GROUP BY

SELECT cities.city_name AS 'City'
     , users.id
     , CONCAT_WS(' ', users.first_name, users.last_name) AS 'Coordinator'
     , COUNT(coordinators_clients.coordinator_id) AS 'Number of Clients' 
  FROM users 
INNER
  JOIN cities 
    ON cities.id = users.geo_id 
LEFT OUTER
  JOIN coordinators_clients
    ON coordinators_clients.coordinator_id = users.id 
 WHERE users.role_id = 2 
GROUP
    BY cities.city_name 
     , users.id 
ORDER 
    BY cities.city_name ASC
     , users.last_name ASC
     , users.first_name ASC

#7

Okay, I now need to add a complication to this - I need to filter this by the created_at date in the clients table. So I will only count the number of clients who registered between a given $start_date and $end_date.

Would I just get all the clients with client_id from the coordinators_clients table, and filter them by the created_at date and use the above query on the results? How would I do that?

SELECT cities.city_name AS 'City'
     , users.id
     , CONCAT_WS(' ', users.first_name, users.last_name) AS 'Coordinator'
     , COUNT(coordinators_clients.coordinator_id) AS 'Number of Clients' 
  FROM users 
INNER
  JOIN cities 
    ON cities.id = users.geo_id 
LEFT OUTER
  JOIN coordinators_clients
    ON coordinators_clients.coordinator_id = users.id
    AND coordinators_clients.client_id IN 
        (SELECT id 
         FROM clients 
         WHERE created_at BETWEEN '$start_date' AND '$end_date')
 WHERE users.role_id = 2 
GROUP
    BY cities.city_name 
     , users.id 
ORDER 
    BY cities.city_name ASC
     , users.last_name ASC
     , users.first_name ASC

Actually, I think that worked.


#8

it works but it might also require an additional pass of the clients table and a temporary file to hold the client ids for subsequent extra join

i would use a CASE expression inside the COUNT


#9

Yes it did give me the results I was expecting. How would the CASE expression work?


#10

it would only count the row if the date meets the range test


#11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.