How to sort by count with postgresql?

user_171160 0
Viewed 27241

I have two tables:

Companies: (id, name, city)
Workers: (id, name)

I would like to get all companies and sort them by numbers of employes.

The result should give:

count | company id | company name | city
------------------------------------------
90         6           foo corp      NY
45         9           bar corp      LA
0          3         foobar corp     HO

I tried:

select 
    c.*, 
    count(w.id) as c 
from 
    companies c 
left join 
    workers w 
on 
    c.id = w.company_id 
group by 
    c.id 
order by 
    c desc;

But that's not working as it tells me to group by g.name too :/

Any ideas?

count join postgresql sql
3 Answers

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

user_2263350
Related