blocks|key|840460|text|您可以尝试按10个单位的价格分组：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|840461|SELECT+COUNT(*)+AS+tally,
+++++++FLOOR(price/10)+AS+prange,
+++++++CONCAT(10*FLOOR(price/10),+"-",+10*FLOOR(price/10)%2B9)+AS+rstr
FROM+my_table
GROUP+BY+prange;|code-block|syntax|javascript|840462|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try grouping by 10 units of price:

<pre><code>SELECT COUNT(*) AS tally,
 FLOOR(price/10) AS prange,
 CONCAT(10*FLOOR(price/10), "-", 10*FLOOR(price/10)+9) AS rstr
FROM my_table
GROUP BY prange;
</code></pre>

blocks|key|840528|text|Kerrek描述的扩展选项，您可以根据案例/时间进行分组|type|unstyled|depth|inlineStyleRanges|entityRanges|data|840529|select
++++++case+when+price+>=+0+and+price+<=+10++++then+'++0+-+10'
+++++++++++when+price+>+10+and+price+<=+50+++then+'+10%2B+-+50'
+++++++++++when+price+>+50+and+price+<=+100++then+'+50%2B+-+100'
+++++++++++else+'over+100'
++++++end+PriceRange,
++++++count(*)+as+TotalWithinRange
+++from
++++++YourTable
+++group+by+1|code-block|syntax|javascript|840530|在这里，"group+by+1“表示select语句中的序数列...在本例中，case/when作为TotalWithinRange。|840531|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

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

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

blocks|key|840597|text|稍微修改一下DRapp的代码..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|840598|select
case+when+price+>=+0+and+price+<+10++++then+"++0+-+10"
+++++++++++when+price+>+10+and+price+<=+50+++then+"+10%2B+-+50"
+++++++++++when+price+>+50+and+price+<=+100++then+"+50%2B+-+100"
+++++++++++else+"over+100"
end+As+PriceRange,
count(item_name)+as+ItemTotal
from+YourTable
group+by+
case+when+price+>=+0+and+price+<+10++++then+"++0+-+10"
+++++++++++when+price+>+10+and+price+<=+50+++then+"+10%2B+-+50"
+++++++++++when+price+>+50+and+price+<=+100++then+"+50%2B+-+100"
+++++++++++else+"over+100"
end;|code-block|syntax|javascript|840599|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A bit of modification of DRapp's code...

<pre><code>select
case when price &gt;= 0 and price &lt; 10 then " 0 - 10"
 when price &gt; 10 and price &lt;= 50 then " 10+ - 50"
 when price &gt; 50 and price &lt;= 100 then " 50+ - 100"
 else "over 100"
end As PriceRange,
count(item_name) as ItemTotal
from YourTable
group by 
case when price &gt;= 0 and price &lt; 10 then " 0 - 10"
 when price &gt; 10 and price &lt;= 50 then " 10+ - 50"
 when price &gt; 50 and price &lt;= 100 then " 50+ - 100"
 else "over 100"
end;
</code></pre>

blocks|key|624846|text|下面是一个简单的mysql解决方案。首先，根据价格值计算存储桶索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|624847|select+*,+floor(price/10)+as+bucket+from+mytable|code-block|syntax|javascript|624848|%2B------%2B-------%2B--------%2B
%7C+name+%7C+price+%7C+bucket+%7C
%2B------%2B-------%2B--------%2B
%7C+i1+++%7C+++++2+%7C++++++0+%7C
%7C+i2+++%7C++++12+%7C++++++1+%7C
%7C+i3+++%7C+++++4+%7C++++++0+%7C
%7C+i4+++%7C++++16+%7C++++++1+%7C
%7C+i5+++%7C+++++6+%7C++++++0+%7C
%2B------%2B-------%2B--------%2B|624849|接下来，按存储桶索引分组，计算价格总和，并设置prange列的格式。|624850|select+concat(bucket+*+10,+'-',+(bucket+*+10+-+1)+%2B+10)+as+prange,+count(price)
from++(select+*,+floor(price/10)+as+bucket+from+mytable)+t1+
group+by+bucket;|624851|%2B--------%2B--------------%2B
%7C+prange+%7C+count(price)+%7C
%2B--------%2B--------------%2B
%7C+0-9++++%7C++++++++++++3+%7C
%7C+10-19++%7C++++++++++++2+%7C
%2B--------%2B--------------%2B|624852|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Here's a simple mysql solution. First, calculate the bucket index based on the price value.
<pre><code>select *, floor(price/10) as bucket from mytable
</code></pre>
<pre><code>+------+-------+--------+
| name | price | bucket |
+------+-------+--------+
| i1 | 2 | 0 |
| i2 | 12 | 1 |
| i3 | 4 | 0 |
| i4 | 16 | 1 |
| i5 | 6 | 0 |
+------+-------+--------+
</code></pre>
Next, group by the bucket index and calculate sum of price and format the prange column.
<pre><code>select concat(bucket * 10, '-', (bucket * 10 - 1) + 10) as prange, count(price)
from (select *, floor(price/10) as bucket from mytable) t1 
group by bucket;
</code></pre>
<pre><code>+--------+--------------+
| prange | count(price) |
+--------+--------------+
| 0-9 | 3 |
| 10-19 | 2 |
+--------+--------------+
</code></pre>

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

For example if my table is:

<pre><code>item_name | price
i1 | 2
i2 | 12
i3 | 4
i4 | 16
i5 | 6
</code></pre>

output:

<pre><code>range | number of item
0 - 10 | 3
10 - 20 | 2
</code></pre>

The way I am doing it so far is 

<pre><code>SELECT count(*)
FROM my_table
Where price &gt;=0
and price &lt;10
</code></pre>

then 

<pre><code>SELECT count(*)
FROM my_table
Where price &gt;=10
and price &lt;20
</code></pre>

and then copy pasting my results each time into excel.

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

sql query that groups different items into buckets

我正在尝试编写一个查询，该查询返回其价格落入certrain存储桶的商品的计数：例如，如果我的表是：item_name | pricei1        | 2i2        | 12i3        | 4i4        | 16i5        | 6输出：range   | number of ite...

问将不同项目分组到存储桶中的sql查询
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将不同项目分组到存储桶中的sql查询EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将不同项目分组到存储桶中的sql查询
EN