I have this table scheme:
我有这个表格方案:
pcode city
1 MAD
3 MAD
3 LON
1 MAD
2 LON
3 MAD
1 LON
2 LON
and I want this result:
我想要这个结果:
pcode cityMAD cityLON
1 2 1
2 0 2
3 2 1
I mean, I need a SELECT
to 'reshape' the table, counting the number of observations for every pcode and city.
我的意思是,我需要一个SELECT来“重塑”表格,计算每个pcode和城市的观察数量。
The table is quite big (>100,000 obs). The number of different pcode's is unknown, but the number of different cities is small, so I can include the names of the cities in the SELECT
.
桌子很大(> 100,000 obs)。不同pcode的数量是未知的,但不同城市的数量很少,所以我可以在SELECT中包含城市的名称。
2 个解决方案
#1
1
You can run the following query:
您可以运行以下查询:
SELECT pcode,
SUM(city='MAD') as cityMAD,
SUM(city='LON') as cityLON
FROM table
GROUP BY pcode
but then you'll need to create all desired columns manually.
但是你需要手动创建所有想要的列。
Maybe you should group by
both pcode
and city
, and use pivot table.
也许您应该按pcode和city分组,并使用数据透视表。
note that SUM
receives 1 when True and 0 when False automatically.
请注意,SUM在True时收到1,在False时自动收到0。
#2
2
You can build query like below:
您可以构建如下查询:
select pcode,
sum(case when city = 'MAD' then 1 ELSE 0 END) as cityMAD,
sum(case when city = 'LON' then 1 ELSE 0 END) as cityLON
from table_name
group by pcode;
You need to add sum
for each city like I have adde for cityMAD and cityLON.
您需要为每个城市添加总和,例如我为cityMAD和cityLON添加的内容。
#1
1
You can run the following query:
您可以运行以下查询:
SELECT pcode,
SUM(city='MAD') as cityMAD,
SUM(city='LON') as cityLON
FROM table
GROUP BY pcode
but then you'll need to create all desired columns manually.
但是你需要手动创建所有想要的列。
Maybe you should group by
both pcode
and city
, and use pivot table.
也许您应该按pcode和city分组,并使用数据透视表。
note that SUM
receives 1 when True and 0 when False automatically.
请注意,SUM在True时收到1,在False时自动收到0。
#2
2
You can build query like below:
您可以构建如下查询:
select pcode,
sum(case when city = 'MAD' then 1 ELSE 0 END) as cityMAD,
sum(case when city = 'LON' then 1 ELSE 0 END) as cityLON
from table_name
group by pcode;
You need to add sum
for each city like I have adde for cityMAD and cityLON.
您需要为每个城市添加总和,例如我为cityMAD和cityLON添加的内容。