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?

Eric · Accepted Answer · 2009-09-09 23:45:58Z

42

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;

answered Sep 9, 2009 at 23:45

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ggolo Over a year ago

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

MaxiWheat Over a year ago

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

Eric Over a year ago

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

Ggolo Over a year ago

ok, I tried the query and it worked great, only one problem, if the company has no workers, it won't appear in the results. Any way to make all companies appear in the results even when they have 0 workers?

Ggolo Over a year ago

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

score 8 · Accepted Answer · 2021-03-03 11:23:17Z

8

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

edited Mar 3, 2021 at 11:23

answered Mar 26, 2019 at 13:50

user226335

Comments

David Andres · Accepted Answer · 2009-09-09 23:50:28Z

1

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

edited Sep 9, 2009 at 23:50

answered Sep 9, 2009 at 23:35

David Andres

31.9k7 gold badges48 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to sort by count with postgresql?

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related