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

toookiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
You can either subquery it

SELECT f1, Major, Minor, Trivial, Total,
   ROUND((ratio_to_report(Major) over())*100, 0) percent_Major,
   ROUND((ratio_to_report(Minor) over())*100, 0) percent_Minor,
   ROUND((ratio_to_report(Trivial) over())*100, 0) percent_Trivial
from
(
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
) X

or repeat the entire expression making up Major in place of MAJOR in the ROUND.. expression

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
toookiAuthor Commented:
Thanks a lot.
I am checking the query that you sent.
toookiAuthor Commented:
Thank you. It worked perfectly for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.