Count of 3 distinct values by country join-CodePudding

I'm trying to figure out how to count how many customers are in each status by country.

I started with joining the two tables customers_customers & customer_status to get the country values for each customer. This will get me a one-off answer for a specific country and status. However, I can't figure out how to count how many customers (by country) are in each status. (Example of desired output below)

SELECT COUNT (customers_customers.customerID)
FROM customers_customers
LEFT JOIN   customer_status
ON customers_customers.customerID = customer_status.customerID
WHERE customers_customers.Country = "US" and customer_status.status = "In Progress";

customers_customers

customerID	country
1	US
2	CA
3	UK
4	GB
5	US

customer_status

customerID	status
1	In Progress
2	Done
3	Not Started
4	Done
5	In Progress

Ultimately, I'm looking for this output:

Country	Not Started	In Progress	Done
US	2	3	1
UK	1	6	2
GB	2	5	1
CA	2	1	7

CodePudding user response：

You can use the aggregate function SUM by condition and group by country.

SELECT
  country,
  SUM(CASE WHEN cs.status = 'Not Started' THEN 1 ELSE 0 END) AS NotStarted,
  SUM(CASE WHEN cs.status = 'In Progress' THEN 1 ELSE 0 END) AS InProgress,
  SUM(CASE WHEN cs.status = 'Done' THEN 1 ELSE 0 END) AS Done
FROM customers_customers cc
JOIN customer_status cs ON cc.customerID = cs.customerID
GROUP BY country

db<>fiddle

CodePudding user response：

dbfiddle

select
  cc.country as "Country",
  count(*) filter (where cs.status = 'Not Started') as "Not Started",
  count(*) filter (where cs.status = 'In Progress') as "In Progress",
  count(*) filter (where cs.status = 'Done') as "Done"
from 
  customers_customers cc left join customer_status cs
    on cc."customerID" = cs."customerID"
group by 1