0

I have 2 tables. 1 is called coaches, the other coach assignments. coach assignments keeps track of what coach is assigned to a customer. When new customers come in I want the coaches with the fewest assignment at the top of a list.

coaches
+user_id
+bio
+title

coach_assignments
+id
+client_id 
+coach_id
+deleted_at

I wrote this code to get the count from the coach_assignments table select count(coach_id), coach_id from coach_assignments where deleted_at is not null group by coach_id order by count(coach_id) ASC

and it worked, however the glaring hole is that a new coach doesn't have any entries in coach_assignments so they aren't counted. I don't want to add a client_count column to coaches as I don't feel coaches should have that info.

I tried:

select  user_id, count(coach_id)
    from  coaches
    left join  
    (
        SELECT  count(coach_id), coach_id
            from  coach_assignments
            where  deleted_at is not null
            group by  coach_id
            order by  count(coach_id) ASC
    ) as counted  ON coaches.user_id = counted.coach_id;

but this only returned 1 row and there should be 4 for my test data. How do I do something like this?

1
select coaches.user_id, coalesce(counted.cnt,0) cnt
from coaches
left join (select count(coach_id) cnt, coach_id 
           from coach_assignments 
           where deleted_at is not null 
           group by coach_id) as counted
     on coaches.user_id = counted.coach_id
order by 2 asc
|improve this answer|||||
  • The problem was that the outer COUNT(...) was not the same as the inner one. Using an alias allows you to make them the same. – Rick James Oct 6 '18 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.