除了需要累加的特定值之外,按照GROUP BY进行分组。

huangapple go评论99阅读模式
英文:

GROUP BY except for specific values that need to be added up

问题

我有这样的一个表格:

  1. id | type
  2. ---+------
  3. 1 | 飞机
  4. 2 | 飞机
  5. 3 | 渡轮
  6. 4 | 渡轮
  7. 5 | 汽车
  8. 6 | 汽车
  9. 7 | 公共汽车
  10. 8 | 火车

我们可以对每种类型进行计数:

  1. select type, count(*) from transport group by type;
  2. 类型 | 计数
  3. ------+------
  4. 飞机 | 2
  5. 渡轮 | 2
  6. 汽车 | 2
  7. 公共汽车 | 1
  8. 火车 | 1

这样做可以,但请注意最后三种类型 - 汽车公共汽车火车 - 都是陆地交通工具(而飞机是空中交通工具,渡轮是海上交通工具)。我真正想要的是:

  1. 类型 | 计数
  2. -------+------
  3. 飞机 | 2
  4. 渡轮 | 2
  5. 汽车 | 4

我可以在应用程序级别汇总计数,但在SQL中是否可行?谢谢。

英文:

I have a table like this:

  1. id | type
  2. ---+------
  3. 1 | plane
  4. 2 | plane
  5. 3 | ferry
  6. 4 | ferry
  7. 5 | car
  8. 6 | car
  9. 7 | bus
  10. 8 | train

We can count the rows for each type:

  1. select type, count(*) from transport group by type;
  2. type | count
  3. ------+------
  4. plane | 2
  5. ferry | 2
  6. car | 2
  7. bus | 1
  8. 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:

  1. type | count
  2. -------+------
  3. plane | 2
  4. ferry | 2
  5. car | 4

I could sum up the counts at the application level, but is this feasible in SQL? Thank you.

答案1

得分: 2

你可以使用 case 表达式将公共汽车和火车重新分配到汽车组:

  1. select
  2. case when type in ('bus', 'train') then 'car' else type end as new_type,
  3. count(*)
  4. from transport
  5. group by new_type
英文:

You can use a case expression to reassign buses and trains to the car group:

  1. select
  2. case when type in ('bus', 'train') then 'car' else type end as new_type,
  3. count(*)
  4. from transport
  5. group by new_type

huangapple
  • 本文由 发表于 2023年6月15日 05:15:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/76477588.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定