user525146 - 2 years ago 71
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 ?

`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);
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download