总小时数和小时总数

Question

我想将0到40之间的所有小时数组合成一个总数。将一个总数和一个总和中的50+加到一个总和中。

select hours, 
     sum(hours) 
from employee 
where hours between 0 and 40 
group by hours; 

上述查询组由小时，所以我已结果由小时裂开，像如果我有1，2.3，0.5，35.5，30等

1  403 
2.3  4.6 
0.5  53 
35.5 284 
30  1230 

但我想要的东西像 403+4.6+53+284+1230 = 1974.6，因为它们都属于40 我该怎么办？

Answer 1

您可以使用条件的聚集，通过建立的小时的间隔值分组。 你的榜样，你可以在40-50组还没有整数值，所以你应该使用明确的关系运算符有，例如，40.1：

select sum(hours), 
     case 
      when hours <= 40 then '0-40' 
      when hours > 40 and hours <= 50 then '41-50' 
      when hours > 50 then '50-...' 
     end 
from employee 
group by case 
      when hours <= 40 then '0-40' 
      when hours > 40 and hours <= 50 then '41-50' 
      when hours > 50 then '50-...' 
     end 

Answer 2

select sum(case when hours between 0 and 40 then hours else 0 end) hours_1, 
     sum(case when hours between 41 and 50 then hours else 0 end) hours_41, 
     sum(case when hours > 50 then hours else 0 end) hours_51 
from employee 

Answer 3

GROUP -ing基于CASE

select (case when hours between 0 and 40 
       then '0 - 40' 
       when hours between 41 and 50 
       then '41 - 50' 
       else 
        '50+' 
      end) as hours_range, 
     sum(hours) 
from employee 
group by (case when hours between 0 and 40 
       then '0 - 40' 
       when hours between 41 and 50 
       then '41 - 50' 
       else 
        '50+' 
      end); 

Answer 4

select '1 to 40',sum(hours) 

from employee 
where hours between 0 and 40 

union all 

    select '41 to 50',sum(hours) 

from employee 
where hours between 41 and 50 

union all 

    select '50+',sum(hours) 

from employee 
where hours>50