How to sort by count with postgresql?

Question

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?

Solution 1

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;

Solution 2

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

Solution 3

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
) T
ORDER BY T.CNT

Comments

[Ggolo]

my table is actually must larger than those two columns (city and name), should I group by all of them?

[MaxiWheat]

I think that yes you should for the GROUP BY clause to work, if not the query will fail

[Eric]

@Ggolo: Any column you're selecting, you have to group by.

[Ggolo]

I have a similar question if you feel like answering it :) stackoverflow.com/questions/1403456/…