Link to home
Create AccountLog in
Avatar of toooki
toooki

asked on

Percentage (group by) calculation on an Oracle table

I have this table in Oracle 11gR2:

create table tab1 (f1 varchar2(100), f2 varchar2(100));
insert into tab1 values ('Team1', 'Major');
insert into tab1 values ('Team2', 'Minor');
insert into tab1 values ('Team3', 'Major');
insert into tab1 values ('Team3', 'Major');
insert into tab1 values ('Team2', 'Major');
insert into tab1 values ('Team2', 'Major');
insert into tab1 values ('Team1', 'Minor');
insert into tab1 values ('Team1', 'Major');
insert into tab1 values ('Team2', 'Major');
insert into tab1 values ('Team2', 'Trivial');
insert into tab1 values ('Team3', 'Major');
insert into tab1 values ('Team1', 'Trivial');
insert into tab1 values ('Team1', 'Minor');
insert into tab1 values ('Team2', 'Minor');
insert into tab1 values ('Team3', 'Trivial');
insert into tab1 values ('Team3', 'Major');
insert into tab1 values ('Team2', 'Trivial');
insert into tab1 values ('Team2', 'Major');
insert into tab1 values ('Team1', 'Trivial');
insert into tab1 values ('Team1', 'Major');
insert into tab1 values ('Team2', 'Minor');
insert into tab1 values ('Team2', 'Trivial');
insert into tab1 values ('Team3', 'Minor');
insert into tab1 values ('Team1', 'Trivial');
commit;

I need a O/P group by the f2 field value of the table:

SELECT p.f1,
SUM(DECODE(p.f2, 'Major', 1, 0)) Major,
SUM(DECODE(p.f2, 'Minor', 1, 0)) Minor,
SUM(DECODE(p.f2, 'Trivial', 1, 0)) Trivial,
SUM(CASE WHEN p.f2 IN ('Major', 'Minor', 'Trivial') THEN 1 END) Total
FROM tab1 p
GROUP BY p.f1

I need to modify the above query so that it outputs 3 additional columns:
perent_Major, percent_Minor, perc_Trivial that will have the percentage values of based on total f2 values:

O/P like:
F1      Major   Minor   Trivial  Total  perent_Major  percent_Minor perc_Trivial
Team2      4      3      3       10     4/11          3/6           3/7
Team1      3      2      3       8      3/11          2/6           3/7
Team3      4      1      1       6      4/11          1/6           1/7

(I mentioned 4/11, 3/6, 3/7, etc. to show the calculation, o/p needs to be calculated values *100)

I tried:

ROUND((ratio_to_report(count(Major)) over())*100, 0) perent_Major,
ROUND((ratio_to_report(count(Minor)) over())*100, 0) percent_Minor,
ROUND((ratio_to_report(count(Trivial)) over())*100, 0) perc_Trivial

But how can I add these as additional column items?
O/P like:
F1      Major   Minor   Trivial  Total  perent_Major  percent_Minor perc_Trivial
Team2	4	3	3	 10     4/11          3/6           3/7
Team1	3	2	3	 8      3/11          2/6           3/7
Team3	4	1	1	 6      4/11          1/6           1/7

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of toooki
toooki

ASKER

Thanks a lot.
I am checking the query that you sent.
Avatar of toooki

ASKER

Thank you. It worked perfectly for me.