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.cityfrom companies c left join workers w on c.id=w.company_id group by w.company_id, c.company_name, c.cityorder 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
Try this as a subquery:
SELECT C.*FROM ( SELECT C.Id, C.Company_Name, C.City, COUNT(W.Id) AS CNT FROM Companies C LEFT JOIN Workers W ON W.Company_Id = C.Id GROUP BY C.Id, C.Company_Name, C.City) TORDER BY T.CNT