CODEWITHSUNDEEP
sqloracle-database
user525146
user525146

Reputation: 3998

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 ?

Upvotes: 1

Views: 107

Answers (4)

saumik gupta
saumik gupta

Reputation: 167

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

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You can use a conditional aggregation, grouping by a value that builds intervals of hours. By your example, you can have not integer values, so you should use explicit relational operators to have, for example, 40.1 in 40-50 group:

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

Upvotes: 2

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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);

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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

Upvotes: 1

Related Questions