具有平均值和公式的Sql时间范围计数
具有平均值和公式的Sql时间范围计数
提问于 2014-04-02 16:21:04
SQl查询返回时间值和计数的特定范围
select
res.range, count(*)
from (select
CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours'
WHEN t1.response_time between 4 and 6 then '4-6 Hours'
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then '8+ Hours'
END as range
from table1 t1) as res
group by res.range下面的SQl查询返回时间值和计数的特定范围
Range | Count
----------------------
4-6 Hours | 24
8+ Hours | 23
6-8 Hours | 22
Within 4 Hours| 7预期输出
Range | Count
----------------------
4-6 Hours | 24
8+ Hours | 23
6-8 Hours | 22
Within 4 Hours| 7
Average | x (Average of Range Time Count)在最后一行的范围中，我想显示平均和平均范围时间计数。如何显示最后一行平均值，以及计算范围时间计数平均值的公式是什么。
回答 2
Stack Overflow用户
发布于 2014-04-02 16:25:06
尝尝这个
select
res.range, count(*)
from (select
CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours'
WHEN t1.response_time between 4 and 6 then '4-6 Hours'
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then '8+ Hours'
END as range
from table1 t1) as res
group by res.range
union all
select 'Average', AVG(counting) from
(
select
res.range, count(*) as counting
from (select
CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours'
WHEN t1.response_time between 4 and 6 then '4-6 Hours'
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then '8+ Hours'
END as range
from table1 t1) as res
group by res.range
) tStack Overflow用户
发布于 2014-04-02 16:25:27
您可以使用with rollup技巧来完成此操作：
select coalesce(res.range, 'Total') as range, avg(cnt) as cnt
from (select res.range, count(*) as cnt
from (select (CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours'
WHEN t1.response_time between 4 and 6 then '4-6 Hours'
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then '8+ Hours'
END) as range
from table1 t1
) res
group by res.range
) r
group by res.range with rollup;外部的group by排序对现有行没有任何作用--单个值的平均值就是该值。但它应该与平均值的总和一致。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22805484
复制相关文章
相似问题
腾讯云开发者
