Home » SQL & PL/SQL » SQL & PL/SQL » CUBE/ROLLUP function
CUBE/ROLLUP function [message #248395] Thu, 28 June 2007 20:39 Go to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
with data as
(
Select 123456 fun_call , 117 col1 , 64 col2 , 1 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 1 factor , 1 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 6 factor , 1 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 7 factor , 2 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 8 factor , 3 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 9 factor , 4 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 10 factor , 5 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 6 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 7 factor , 2 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 8 factor , 3 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 9 factor , 4 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 10 factor , 5 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 6 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 7 factor , 2 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 8 factor , 3 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 9 factor , 4 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 10 factor , 5 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 14 factor , 125 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 14 factor , 116 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 14 factor , 116 cost from dual
)
select * from data

I would like to calculate sum(cost) based on the rollups based on col1, col2 where factor=14. Also want to calculate the count of distinct fun_call based on rollups based on col1, col2 where factor=9.

Thanks
Re: CUBE/ROLLUP function [message #248434 is a reply to message #248395] Fri, 29 June 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Please post what the result should be (formatted of course).

Regards
Michel
Re: CUBE/ROLLUP function [message #248577 is a reply to message #248395] Fri, 29 June 2007 10:48 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Use the first query to retrive sum(cost):

with data as
(
Select 123456 fun_call , 117 col1 , 64 col2 , 1 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 1 factor , 1 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 6 factor , 1 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 7 factor , 2 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 8 factor , 3 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 9 factor , 4 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 10 factor , 5 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 6 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 7 factor , 2 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 8 factor , 3 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 9 factor , 4 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 10 factor , 5 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 6 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 7 factor , 2 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 8 factor , 3 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 9 factor , 4 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 10 factor , 5 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 14 factor , 125 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 14 factor , 116 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 14 factor , 116 cost from dual
)
select sum(cost) from data   where factor=14 group by rollup(col1,col2)


And for get count(fun_call) use the below query:

with data as
(
Select 123456 fun_call , 117 col1 , 64 col2 , 1 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 1 factor , 1 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 6 factor , 1 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 7 factor , 2 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 8 factor , 3 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 9 factor , 4 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 10 factor , 5 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 6 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 7 factor , 2 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 8 factor , 3 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 9 factor , 4 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 10 factor , 5 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 6 factor , 1 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 7 factor , 2 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 8 factor , 3 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 9 factor , 4 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 10 factor , 5 cost from dual union all
Select 123456 fun_call , 117 col1 , 64 col2 , 14 factor , 125 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 14 factor , 116 cost from dual union all
Select 789789 fun_call , 148 col1 , 95 col2 , 14 factor , 116 cost from dual
)              
select count(FUN_CALL) from data  where factor=9 group by rollup(col1,col2)


Regards
Sanka Neutral
Re: CUBE/ROLLUP function [message #248613 is a reply to message #248577] Fri, 29 June 2007 13:48 Go to previous message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
Here is the new data set and the result set I would like to get using the cube function on col1, col2 where factor=1 and also find out count (distinct token_key) based on cube(col1, col2), I am not sure if i need to take factor into cube function also.

with data as
(select 12345 token_key, 15009 col1, 5000 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 17586 col1, 5084 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 18616 col1, 5091 col2, 4 factor_key, .61 cost from dual union all 
select 12345 token_key, 15513 col1, 5022 col2, 4 factor_key, 1.05 cost from dual union all 
select 12345 token_key, 15237 col1, 5010 col2, 4 factor_key, .32 cost from dual union all 
select 12345 token_key, 16672 col1, 5067 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 17130 col1, 5078 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 18042 col1, 5087 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 15009 col1, 5000 col2, 4 factor_key, .32 cost from dual union all 
select 12345 token_key, 16145 col1, 5045 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 17586 col1, 5084 col2, 4 factor_key, .31 cost from dual union all 
select 12345 token_key, 18957 col1, 5096 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16878 col1, 5072 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16672 col1, 5067 col2, 4 factor_key, .37 cost from dual union all 
select 12345 token_key, 17130 col1, 5078 col2, 4 factor_key, .32 cost from dual union all 
select 12345 token_key, 18042 col1, 5087 col2, 4 factor_key, .31 cost from dual union all 
select 12345 token_key, 15900 col1, 5040 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16145 col1, 5045 col2, 4 factor_key, .33 cost from dual union all 
select 12345 token_key, 17772 col1, 5085 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16215 col1, 5050 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16878 col1, 5072 col2, 4 factor_key, .31 cost from dual union all 
select 12345 token_key, 15900 col1, 5040 col2, 4 factor_key, .33 cost from dual union all 
select 12345 token_key, 18616 col1, 5091 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16373 col1, 5059 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 15237 col1, 5010 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 15513 col1, 5022 col2, 1 factor_key, 1 cost from dual union all 
select 12345 token_key, 16215 col1, 5050 col2, 4 factor_key, .34 cost from dual 
)
select * from data


Results should be like below

with result as
(select 15009 col1, 5000 col2, 1 factor, 67199 cost from dual union all
select 15009 col1, 5000 col2, 4 factor, 21503.68 cost from dual union all
select 15237 col1, 5010 col2, 1 factor, 94892 cost from dual union all
select 15237 col1, 5010 col2, 4 factor, 30365.44 cost from dual union all
select 15513 col1, 5022 col2, 1 factor, 45451 cost from dual union all
select 15513 col1, 5022 col2, 4 factor, 47723.55 cost from dual union all
select 15900 col1, 5040 col2, 1 factor, 95026 cost from dual union all
select 15900 col1, 5040 col2, 4 factor, 31358.58 cost from dual union all
select 16145 col1, 5045 col2, 1 factor, 143195 cost from dual union all
select 16145 col1, 5045 col2, 4 factor, 47254.35 cost from dual union all
select 16215 col1, 5050 col2, 1 factor, 116299 cost from dual union all
select 16215 col1, 5050 col2, 4 factor, 39541.66 cost from dual union all
select 16373 col1, 5059 col2, 1 factor, 11584 cost from dual union all
select 16672 col1, 5067 col2, 1 factor, 20325 cost from dual union all
select 16672 col1, 5067 col2, 4 factor, 7520.25 cost from dual union all
select 16878 col1, 5072 col2, 1 factor, 11893 cost from dual union all
select 16878 col1, 5072 col2, 4 factor, 3686.83 cost from dual union all
select 17130 col1, 5078 col2, 1 factor, 25655 cost from dual union all
select 17130 col1, 5078 col2, 4 factor, 8209.6 cost from dual union all
select 17586 col1, 5084 col2, 1 factor, 12350 cost from dual union all
select 17586 col1, 5084 col2, 4 factor, 3828.5 cost from dual union all
select 17772 col1, 5085 col2, 1 factor, 27641 cost from dual union all
select 18042 col1, 5087 col2, 1 factor, 20921 cost from dual union all
select 18042 col1, 5087 col2, 4 factor, 6485.51 cost from dual union all
select 18616 col1, 5091 col2, 1 factor, 9045 cost from dual union all
select 18616 col1, 5091 col2, 4 factor, 5517.45 cost from dual union all
select 18957 col1, 5096 col2, 1 factor, 16280 cost from dual
)
select * from result



Thanks
Previous Topic: Index Usage
Next Topic: data in (insert into XXDSC_REVENUE_REPORT_DETAIL_MV) is not inserting
Goto Forum:
  


Current Time: Thu Dec 08 16:38:11 CST 2016

Total time taken to generate the page: 0.08534 seconds