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(cou nt(Major)) over())*100, 0) perent_Major,
ROUND((ratio_to_report(cou nt(Minor)) over())*100, 0) percent_Minor,
ROUND((ratio_to_report(cou nt(Trivial )) over())*100, 0) perc_Trivial
But how can I add these as additional column items?
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(cou
ROUND((ratio_to_report(cou
ROUND((ratio_to_report(cou
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you. It worked perfectly for me.
ASKER
I am checking the query that you sent.