新列的Sql和
新列的Sql和
提问于 2018-06-05 08:59:23
在下面的查询中，我根据条件创建了新列：
select
sum(case when Overall_Time_Spent < 0 then 1 else 0 end) as Errors,
sum(case when Overall_Time_Spent between 0 and 3 then 1 else 0 end) as _0_3_days,
sum(case when Overall_Time_Spent = 4 then 1 else 0 end) as _4_days,
sum(case when Overall_Time_Spent = 5 then 1 else 0 end) as _5_days,
sum(case when Overall_Time_Spent between 6 and 8 then 1 else 0 end) as _6_8_days,
sum(case when Overall_Time_Spent >= 9 then 1 else 0 end) as more_than_9_days,
avg(case when Overall_Time_Spent < 0 then 100.0 else 0 end) as Errors_percent,
avg(case when Overall_Time_Spent between 0 and 3 then 100.0 else 0 end) as _0_3_percent,
avg(case when Overall_Time_Spent = 4 then 100.0 else 0 end) as _4_percent,
avg(case when Overall_Time_Spent = 5 then 100.0 else 0 end) as _5_percent,
avg(case when Overall_Time_Spent between 6 and 8 then 100.0 else 0 end) as _6_8_percent,
avg(case when Overall_Time_Spent >= 9 then 100.0 else 0 end) as more_than_9_days_percent,我如何在这个查询中添加一个查询，其中我可以再添加两个列，给出所有和所有avg的和。
提前感谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-05 09:02:31
您拥有的CASE表达式涵盖了<0、0、8和>=9之间的表达式，后者是every ，前提是您的数据是int。因此，您所需要添加的就是
COUNT(Overall_Time_Spent) AS DaysTotal如果它不是int，那么您的CASE表达式将丢失3和4、4和5、5和6以及8和9之间的值，但是COUNT将包括它们。
Stack Overflow用户
发布于 2018-06-05 09:03:56
尝试使用子查询：
SELECT a.*, (Errors + _0_3_days...) as Total FROM (
select
sum(case when Overall_Time_Spent < 0 then 1 else 0 end) as Errors,
sum(case when Overall_Time_Spent between 0 and 3 then 1 else 0 end) as _0_3_days,
sum(case when Overall_Time_Spent = 4 then 1 else 0 end) as _4_days,
sum(case when Overall_Time_Spent = 5 then 1 else 0 end) as _5_days,
sum(case when Overall_Time_Spent between 6 and 8 then 1 else 0 end) as _6_8_days,
sum(case when Overall_Time_Spent >= 9 then 1 else 0 end) as more_than_9_days
) as a页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50696140
复制相关文章
相似问题
腾讯云开发者
