CODEWITHSUNDEEP
sqloracle-databaserollup
vamsivanka
vamsivanka

Reputation: 792

How to use rollup function in oracle

I am trying to use a group by rollup oracle function, but not getting it right.

Here is my data format (L1_Proj_ID is Level 1 Project ID etc.....)

Proj_ID  Hours_Charged L1_Proj_ID  L2_Proj_ID L3_Proj_ID     L4_Proj_ID  
-------------------------------------------------------------------------  
1100.10         20       1100        1100.10    Null           Null  
1100.11.01      30       1100        1100.11    1100.11.01     Null  
1100.11.02      40       1100        1100.11    1100.11.02     Null  
1100.12.01.01   50       1100        1100.12    1100.12.01     1100.12.01.01

I need to get the roll up totals and my output should be 

Proj_Level  Hours_Charged  
--------------------------  
1100            140  
1100.10          20  
1100.11          70  
1100.11.01       30  
1100.11.02       40   
1100.12          50   
1100.12.01       50  
1100.12.01.01    50

Please, Let me know if there any other easy way to do.

As of now i can get this data like... 

select 
  L1_proj_id, 
  sum(hours_charged) as hours_charged 
from table  
group by 
  l1_proj_id 
union all   
select 
  L2_proj_id, 
  sum(hours_charged) as hours_charged 
from table 
group by 
  21_proj_id  
union all  
select 
  L3_proj_id, 
  sum(hours_charged) as hours_charged 
from table 
group by 
  l3_proj_id  
union all  
select 
  L4_proj_id, 
  sum(hours_charged) as hours_charged 
from table 
group by 
  l4_proj_id

Upvotes: 1

Views: 634

Answers (1)

Hambone
Hambone

Reputation: 16377

This does not use rollup, but I think it might deliver your results. In essence, I unnest your columns into rows. It should also be relatively easy to scale if your content changes.

with levels as (
  select level id
  from dual
  connect by level <= 4
),
all_data as (
  select
    case l.id
      when 1 then l1_proj_id
      when 2 then l2_proj_id
      when 3 then l3_proj_id
      when 4 then l4_proj_id
    end as project_id,
    t.hours_charged
  from
    table t,
    levels l
)
select
  project_id, sum (hours_charged) as hours_charged
from all_data
where project_id is not null
group by project_id

Upvotes: 1

Related Questions