postgreSQL Query - Count column values matching on two columns
I need help creating an SQL query to count rows broken out on two separate columns.
This is the DDL for my Tables:
CREATE TABLE Agency (
id SERIAL not null,
city VARCHAR(200) not null,
PRIMARY KEY(id)
);
CREATE TABLE Customer (
id SERIAL not null,
fullname VARCHAR(200) not null,
status VARCHAR(15) not null CHECK(status IN ('new','regular','gold')),
agencyID INTEGER not null REFERENCES Agency(id),
PRIMARY KEY(id)
);
Sample Data from the Tables
AGENCY
id|'city'
1 |'London'
2 |'Moscow'
3 |'Beijing'
CUSTOMER
id|'fullname' |'status' |agencyid
1 |'Michael Smith' |'new' |1
2 |'John Doe' |'regular'|1
3 |'Vlad Atanasov' |'new' |2
4 |'Vasili Karasev'|'regular'|2
5 |'Elena Miskova' |'gold' |2
6 |'Kim Yin Lu' |'new' |3
7 |'Hu Jintao' |'regular'|3
8 |'Wen Jiabao' |'regular'|3
I want to count the new customers, regular customers and gold_customers by city.
I need to count separately for ('new','regular','gol开发者_如何学编程d'). Here is the output I want:
'city' |new_customers|regular_customers|gold_customers
'Moscow' |1 |1 |1
'Beijing'|1 |2 |0
'London' |1 |1 |0
I was struggling with the same a couple of weeks ago.
This is what you need.
SELECT
Agency.city,
count(case when Customer.status = 'new' then 1 else null end) as New_Customers,
count(case when Customer.status = 'regular' then 1 else null end) as Regular_Customers,
count(case when Customer.status = 'gold' then 1 else null end) as Gold_Customers
FROM
Agency, Customer
WHERE
Agency.id = Customer.agencyID
GROUP BY
Agency.city;
You could group on city
, and then sum the number of statuses in each city:
select city
, sum(case when c.status = 'new' then 1 end) as New
, sum(case when c.status = 'regular' then 1 end) as Regular
, sum(case when c.status = 'gold' then 1 end) as Gold
from customer c
join agency a
on c.agencyid = a.id
group by
a.city
精彩评论