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?

Answer 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;