sql - Analytical function using group by clause -

sql - Analytical function using group by clause -

i want grouping info level , apply analytical function percentage contribution of new level on level above this:-

i have table temp_analytic info follows. info in table follows heirarchy (portfolio , inv_num has 1 many relation , inv_num , loan_num has 1 many relationship.

loan_num roll-up inv_num , inv_num farther roll-up portfolio

so basic info in @ loan level. grouping info investor level , see percentage contribution of each investor portfolio

base data

loan_num inv_num portfolio balance 1111 1 10 2222 1 20 3333 1 30![enter image description here][1] 4444 2 40 5555 2 50 6666 2 60 7777 3 b 70 8888 3 b 80 9999 3 b 90

i using analytical function sum on partition portfolio

query

select loan_num, inv_num, portfolio, balance, round(balance/sum(balance) on (partition portfolio),4.2)*100 portfolio_perc temp_analytic order portfolio

result

loan_num inv_num portfolio balance portfolio_perc 1111 1 10 4.76 2222 1 20 9.52 3333 1 30 14.29 4444 2 40 19.05 5555 2 50 23.81 6666 2 60 28.57 7777 3 b 70 29.17 8888 3 b 80 33.33 9999 3 b 90 37.50

so able percentage contribution of loan portfolio

if seek calculate percentage contrbution of inv_num portfolio. getting error "not grouping function" using below query obvious. want know can roll-up or grouping info 1 level , apply analytical functions ( partitioning higher level) ?

select inv_num, sum(balance), round(balance/sum(balance) on (partition portfolio),4.2)*100 portfolio_perc delete_srini grouping inv_num order portfolio

what best way it

the results should this

inv_num portfolio balance portfolio_perc 1 60 28.60 2 150 71.40 3 b 240 100.00

please help.

you have right idea. but, utilize subquery aggregate info first , utilize analytic function:

select inv_num, portfolio, balance, round(balance/sum(balance) on (partition portfolio),4.2)*100 portfolio_perc (select inv_num, portfolio, sum(balance) balance temp_analytic ta grouping inv_num, portfolio ) ta order inv_num;

you can in 1 shot -- without subquery. reading , writing queries analytic functions , aggregation takes getting used to:

select inv_num, portfolio, sum(balance) balance, round(sum(balance)/sum(sum(balance)) on (partition portfolio),4.2)*100 portfolio_perc temp_analytic ta grouping inv_num, portfolio order inv_num;

sql