How to sort by count with postgresql?
You've aliased the table and column as the same thing, so don't do that. It's not invalid, just tough to follow.
Anyway, include all columns that you're selecting that aren't aggregates in your group by
:
select
count(w.id) as mycount,
w.company_id,
c.company_name,
c.city
from
companies c
left join workers w on
c.id=w.company_id
group by
w.company_id,
c.company_name,
c.city
order by mycount desc;
If you don't want the count result to be returned (because of an ORM framework or so), you could apply it directly in the order by clause:
select
c.*
from
companies c
left join
workers w
on
c.id = w.company_id
group by
c.id
order by
count(w.id) desc;
Tested with postgreSQL 11