Hello PL/SQL Gurus/experts,
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
I have following table -
drop table t2;
create table t2(Pat_NM,Hospital,Test_Range,Total) as select
'Andy','Batra','> 10 Mph','20000' from dual union all select
'Andy','Fortis','1-3 Mph','24500' from dual union all select
'Andy','Max','5-10 Mph','10600' from dual union all select
'Andy','Max','5-10 Mph','22500' from dual union all select
'Andy','Aashiana','5-10 Mph','110600' from dual union all select
'Andy','Amar','5-10 Mph','34800' from dual union all select
'Andy','Max','5-10 Mph','600' from dual union all select
'Andy','Columbia','< 1 Mph','27700' from dual union all select
'Andy','Nimhans','< 1 Mph','50000' from dual union all select
'Andy','Meenam','< 1 Mph','11000' from dual union all select
'Andy','Meeran','5-10 Mph','24625' from dual union all select
'Andy','Mnagamani','> 10 Mph','12000' from dual union all select
'Andy','Murari','> 10 Mph','20600' from dual union all select
'Andy','Triveni','5-10 Mph','16500' from dual union all select
'Cindy','Batra','5-10 Mph','14700' from dual union all select
'Cindy','Max','< 1 Mph','170000' from dual union all select
'Cindy','Apollo Medical Centre','> 10 Mph','19000' from dual union all select
'Cindy','MLal','1-3 Mph','22600' from dual union all select
'Cindy','Columbia','< 1 Mph','28900' from dual union all select
'Cindy','Asian','1-3 Mph','27900' from dual union all select
'Cindy','Mahagun','< 1 Mph','28700' from dual union all select
'Cindy','Manipal','< 1 Mph','29040' from dual union all select
'Cindy','Prestige','< 1 Mph','12700' from dual union all select
'Cindy','A.G.M.','< 1 Mph','97800' from dual union all select
'Cindy','Shobha','< 1 Mph','700' from dual union all select
'Cindy','Aashiana','5-10 Mph','23450' from dual union all select
'Cindy','Amar','1-3 Mph','21325' from dual union all select
'Cindy','Childs Trust','5-10 Mph','22775' from dual union all select
'Cindy','Crescent ','< 1 Mph','20025' from dual;
I want to extract the data based on the condition that -
Within each Test_Range get the subtotal of all of the records and display only the top 3 Hospitals
and in the end do the grand total.
For this i am using the following query -
select Pat_NM,
Hospital,
SUM (Total) "Total",
decode(grouping(Test_Range), 0, Test_Range, 'Total') "Test Range",
decode(grouping(Test_Range), 0, max(rank), null) Rank
from
(
SELECT Pat_NM,
Hospital,
Test_Range,
SUM (Total) Total,
DENSE_RANK ()
OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (Total) DESC)
AS RANK,
ROW_NUMBER ()
OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (Total) DESC)
AS rk
FROM t2
GROUP BY Pat_NM, Hospital, Test_Range
)
where rank <=3
group by grouping sets((Pat_NM, Hospital, Test_Range),())
order by Pat_NM,Test_Range, Rank;
Result i am getting is -
PAT_N Test Ran HOSPITAL Total RANK
----- -------- --------------------- ---------- ----------
Andy 1-3 Mph Fortis 24500 1
Andy 5-10 Mph Aashiana 110600 1
Andy 5-10 Mph Amar 34800 2
Andy 5-10 Mph Max 33700 3
Andy < 1 Mph Nimhans 50000 1
Andy < 1 Mph Columbia 27700 2
Andy < 1 Mph Meenam 11000 3
Andy > 10 Mph Murari 20600 1
Andy > 10 Mph Batra 20000 2
Andy > 10 Mph Mnagamani 12000 3
Cindy 1-3 Mph Asian 27900 1
PAT_N Test Ran HOSPITAL Total RANK
----- -------- --------------------- ---------- ----------
Cindy 1-3 Mph MLal 22600 2
Cindy 1-3 Mph Amar 21325 3
Cindy 5-10 Mph Aashiana 23450 1
Cindy 5-10 Mph Childs Trust 22775 2
Cindy 5-10 Mph Batra 14700 3
Cindy < 1 Mph Max 170000 1
Cindy < 1 Mph A.G.M. 97800 2
Cindy < 1 Mph Manipal 29040 3
Cindy > 10 Mph Apollo Medical Centre 19000 1
Total 793490
21 rows selected.
Whereas i am looking forward for a result as -
PAT_N Test Ran HOSPITAL Total RANK
----- -------- --------------------- ---------- ----------
Andy 1-3 Mph Fortis 24500 1
Subtotal 24500
Andy 5-10 Mph Aashiana 110600 1
Andy 5-10 Mph Amar 34800 2
Andy 5-10 Mph Max 33700 3
Subtotal 220225
Andy < 1 Mph Nimhans 50000 1
Andy < 1 Mph Columbia 27700 2
Andy < 1 Mph Meenam 11000 3
Subtotal 88700
Andy > 10 Mph Murari 20600 1
Andy > 10 Mph Batra 20000 2
PAT_N Test Ran HOSPITAL Total RANK
----- -------- --------------------- ---------- ----------
Andy > 10 Mph Mnagamani 12000 3
Subtotal 52600
Cindy 1-3 Mph Asian 27900 1
Cindy 1-3 Mph MLal 22600 2
Cindy 1-3 Mph Amar 21325 3
Subtotal 71825
Cindy 5-10 Mph Aashiana 23450 1
Cindy 5-10 Mph Childs Trust 22775 2
Cindy 5-10 Mph Batra 14700 3
Subtotal 60925
Cindy < 1 Mph Max 170000 1
Cindy < 1 Mph A.G.M. 97800 2
Cindy < 1 Mph Manipal 29040 3
Subtotal 387865
PAT_N Test Ran HOSPITAL Total RANK
----- -------- --------------------- ---------- ----------
Cindy > 10 Mph Apollo Medical Centre 19000 1
Subtotal 19000
Total 925640
29 rows selected.
I am thankful to you in advance for your valuable time and effort.