oracle sql trick - Get distinct records from partitioned data

Dear Readers,

I was in a situation, where I needed to get distinct records from group/subgroup partitioned data.

Please note the below sqls can be executed on livesql.oracle.com. This is tested in 19c :)

Query link:  https://livesql.oracle.com/apex/livesql/s/ilxt7f2vd91u5tii3eld2brxn

Row source:

select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100

Partitioning:

select md_lvl2 
,md_lvl3 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc) sumrec 
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)

Final Query:

select distinct(md_lvl2||','||md_lvl3||','||sumrec) 
from 
( 
select md_lvl2 
,md_lvl3 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc) sumrec 
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)) 
order by 1;

Result: 

(MD_LVL2||','||MD_LVL3||','||SUMREC)
0,0,2450
0,1,1634
0,2,850
1,0,2500
1,1,1633
1,2,800

oracle sql trick - subgroup/group - percentage

Dear Readers,

I was in a situation, where I needed to form group,subgroup on a resultset. This is normally very easy in pivot operations in excel/google sheet.

Please note the below sqls can be executed on livesql.oracle.com. This is tested in 19c :)

How to get this done using oracle sql. I am writing this blog to just show that.

Query 1 - row source:

select mod(level,2),mod(level,3),level
from dual
connect by level < 10
order by 1,2;

result:
"MOD(LEVEL,2)","MOD(LEVEL,3)",LEVEL
0,0,6
0,1,4
0,2,2
0,2,8
1,0,3
1,0,9
1,1,1
1,1,7
1,2,5


query 2 - which perform grouping of lvl2 & lvl3 columns:

select md_lvl2,md_lvl3,sum(lvl)
from
(
select mod(level,2) md_lvl2,mod(level,3) md_lvl3,level lvl
from dual
connect by level < 10
)
group by md_lvl2,md_lvl3
order by  md_lvl2,md_lvl3;

result:
MD_LVL2,MD_LVL3,SUM(LVL)
0,0,6
0,1,4
0,2,10
1,0,12
1,1,8
1,2,5

See here I can get grouping done @ 1 level which is @ both the columns, but now my requirement is I need a ratio or percentage of this resultant sum over lvl2 aggregation. Hope you follow me

for example, I need the ratio of sum(lvl) aggregated over md_lvl2,md_lvl3 over sum(lvl) aggregated over md_lvl2, kind a rollup - but here I need percentage.

Query 3 - the trick/the sql for requirement:

with t as 
(
select mod(level,2) md_lvl2,mod(level,3) md_lvl3,level lvl
from dual
connect by level < 10
)
select md_lvl2
,md_lvl3
,sum_lvl3
,sum_lvl2
,round(100*sum_lvl3/sum_lvl2,2) ratio
from
(
select md_lvl2
,md_lvl3
,sum(lvl) over (partition by md_lvl2,md_lvl3) sum_lvl3
,sum(lvl) over (partition by md_lvl2) sum_lvl2
from t
)
group by md_lvl2
,md_lvl3
,sum_lvl3
,sum_lvl2
order by 1,2;

Query:
MD_LVL2,MD_LVL3,SUM_LVL3,SUM_LVL2,RATIO
0,0,6,20,30
0,1,4,20,20
0,2,10,20,50
1,0,12,25,48
1,1,8,25,32
1,2,5,25,20

See if you notice we get the percentage out now. The group by expression is mandatory so that you get unique records out. Otherwise all 9 records gets with duplicate on md_lvl2,md_lvl3 gets printed, but it doesn harm the percentage or sum.

Thanks for paying a visit.