sql查询将不同项目分组到存储桶中 [英] sql query that groups different items into buckets

问题描述

我正在尝试编写一个查询，该查询返回价格落入特定时段的商品的数量：

I am trying to write a query that returns the count of items whose price falls into certrain buckets:

例如，如果我的表是：

item_name | price
i1        | 2
i2        | 12
i3        | 4
i4        | 16
i5        | 6

输出：

range   | number of item
0 - 10  |  3
10 - 20 |  2

到目前为止，我的操作方式是

The way I am doing it so far is

SELECT count(*)
FROM my_table
Where price >=0
and price <10

然后

SELECT count(*)
FROM my_table
Where price >=10
and price <20

，然后每次将粘贴的结果复制到excel中。

and then copy pasting my results each time into excel.

在SQL查询中是否有自动的方法？

Is there an automatic way to do this in an sql query?

推荐答案

Kerrek所描述的扩展选项，您可以根据情况/时间

An expanded option from what Kerrek described, you can do you grouping based on a case/when

select
      case when price >= 0 and price <= 10    then '  0 - 10'
           when price > 10 and price <= 50   then ' 10+ - 50'
           when price > 50 and price <= 100  then ' 50+ - 100'
           else 'over 100'
      end PriceRange,
      count(*) as TotalWithinRange
   from
      YourTable
   group by 1

此处， 1分组代表您的序号列select语句...在这种情况下，为TotalWithinRange。

Here, the "group by 1" represents the ordinal column in your select statement... in this case, the case/when as TotalWithinRange.

这篇关于sql查询将不同项目分组到存储桶中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！