我正在尝试编写一个查询,该查询返回其价格落入certrain存储桶的商品的计数:
例如,如果我的表是:
item_name | price
i1 | 2
i2 | 12
i3 | 4
i4 | 16
i5 | 6
输出:
range | number of item
0 - 10 | 3
10 - 20 | 2
到目前为止,我这样做的方式是
SELECT count(*)
FROM my_table
Where price >=0
and price <10
然后
SELECT count(*)
FROM my_table
Where price >=10
and price <20
然后将每次粘贴的结果复制到excel中。
有没有一种在sql查询中自动执行此操作的方法?
发布于 2011-07-13 03:44:44
Kerrek描述的扩展选项,您可以根据案例/时间进行分组
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
在这里,"group by 1“表示select语句中的序数列...在本例中,case/when作为TotalWithinRange。
发布于 2011-07-13 03:15:34
您可以尝试按10个单位的价格分组:
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;
发布于 2011-07-13 04:16:00
稍微修改一下DRapp的代码..。
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 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+ - 50"
when price > 50 and price <= 100 then " 50+ - 100"
else "over 100"
end;
https://stackoverflow.com/questions/6669730
复制相似问题