我需要帮助创建一个SQL查询来计算在两个单独的列上分列的行.
这是我的表的DDL：
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)
);
表中的样本数据
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
我想按城市计算新客户,普通客户和黄金客户.
我需要单独计算(‘新’,’常规’,’黄金’).这是我想要的输出：
'city' |new_customers|regular_customers|gold_customers 'Moscow' |1 |1 |1 'Beijing'|1 |2 |0 'London' |1 |1 |0
几周前我一直在挣扎着.
这就是你所需要的.
原文链接：https://www.f2er.com/postgresql/192721.html这就是你所需要的.
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;
