How to sort by custom count with postgresql?

Question

I have two tables:

Companies: (id, name)

Workers: (id, name, country)

I would like to get all companies and sort them by numbers of employees from a given country.

For a query looking for companies that have more workers from the USA, the result should give:

#workers from USA | company id | company name 
----------------------------------------------
        90              6           foo corp     
        45              9           bar corp     
        0               3         foobar corp

I tried:

select 
    count(w.country='USA') as mycount,
    w.company_id,
    c.company_name
from 
    companies c 
    left join workers w on 
        c.id=w.company_id 
group by 
    w.company_id,
    c.company_name
order by mycount desc;

But that's counting all workers regardless of their countries.

Any ideas?

Answer 1

Does replacing COUNT(w.country='USA') with COUNT(*) help? I believe your query needs to refer to fields on the Companies table as opposed to workers because your left join leaves open the possibility that the set of workers pulled for a specific company/country is the empty set.

Try:

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

Answer 2

You can do that easily with a correlated subquery:

SELECT
  (SELECT count(*) FROM workers w WHERE w.company_id=c.id AND w.country='USA') AS mycount,
  c.id,
  c.company_name
FROM
  companies c
ORDER BY mycount DESC