英文:
GROUP BY except for specific values that need to be added up
问题
我有这样的一个表格：
id | type
---+------
1 | 飞机
2 | 飞机
3 | 渡轮
4 | 渡轮
5 | 汽车
6 | 汽车
7 | 公共汽车
8 | 火车
我们可以对每种类型进行计数：
select type, count(*) from transport group by type;
类型 | 计数
------+------
飞机 | 2
渡轮 | 2
汽车 | 2
公共汽车 | 1
火车 | 1
这样做可以，但请注意最后三种类型 - 汽车，公共汽车，火车 - 都是陆地交通工具（而飞机是空中交通工具，渡轮是海上交通工具）。我真正想要的是：
类型 | 计数
-------+------
飞机 | 2
渡轮 | 2
汽车 | 4
我可以在应用程序级别汇总计数，但在SQL中是否可行？谢谢。
英文:
I have a table like this:
id | type
---+------
1 | plane
2 | plane
3 | ferry
4 | ferry
5 | car
6 | car
7 | bus
8 | train
We can count the rows for each type:
select type, count(*) from transport group by type;
type | count
------+------
plane | 2
ferry | 2
car | 2
bus | 1
train | 1
This works, but notice how the last three types - car, bus, train - are ground transport (while plane is air and ferry is sea). What I really want is:
type | count
-------+------
plane | 2
ferry | 2
car | 4
I could sum up the counts at the application level, but is this feasible in SQL? Thank you.
答案1
得分: 2
你可以使用 case 表达式将公共汽车和火车重新分配到汽车组：
select
case when type in ('bus', 'train') then 'car' else type end as new_type,
count(*)
from transport
group by new_type
英文:
You can use a case expression to reassign buses and trains to the car group:
select
case when type in ('bus', 'train') then 'car' else type end as new_type,
count(*)
from transport
group by new_type
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论