user525146 user525146 - 7 months ago 20
SQL Question

Sum the total hours and group by hours

I want to group by all the hours between 0 and 40 into one total sum.
41 - 50 into one total sum and 50+ into another sum.

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


The above query groups by the hours, so i have the results split by hours, like if I have 1, 2.3, 0.5, 35.5, 30 etc.

1 403
2.3 4.6
0.5 53
35.5 284
30 1230


But I want something like
403+4.6+53+284+1230 = 1974.6
because they all fall under 40
How can I do it ?

Answer

GROUP-ing based on 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);