How to join 2 tables when calling a count on the second table 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?
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