Home » SQL & PL/SQL » SQL & PL/SQL » Calculate percentage based on month (Oracle 11.2.0.3.0)
Calculate percentage based on month [message #658800] |
Tue, 27 December 2016 08:56 |
|
amarjadhav
Messages: 40 Registered: April 2011 Location: bangalore
|
Member |
|
|
Hi All,
I have table structure like below,
DESC TEST;
Name Null Type
------------------------------ -------- --------------
TYPE VARCHAR2(80)
MONTH VARCHAR2(15)
TAGNAME VARCHAR2(15)
COUNT NUMBER(16)
TAG_RANGE NUMBER(16)
SELECT * FROM TEST;
NAME MONTH RANGE FREQ_RANGE COUNT
A Sep-16 <500 500 10
A Sep-16 <1000 1000 30
B Sep-16 <500 500 20
B Sep-16 <1000 1000 40
A Oct-16 <1000 1000 20
A Oct-16 <500 500 30
B Oct-16 <1000 1000 10
B Oct-16 <500 500 40
Expected output like below with extra column PERC%,here i need to calculate percentage based of NAME and MONTH.
NAME MONTH RANGE FREQ_RANGE COUNT PERC%
A Sep-16 <500 500 10 25 = 10*100/40 where 40 is sum(count) for month SEP-16 and NAME "A"
A Sep-16 <1000 1000 30 75 = 30*100/40
B Sep-16 <500 500 20 33.33333333 = 20*100/60 where 60 is sum(count) for month SEP-16 and NAME "B"
B Sep-16 <1000 1000 40 66.66666667 = 40*100/50
A Oct-16 <1000 1000 20 40
A Oct-16 <500 500 30 60
B Oct-16 <1000 1000 10 20
B Oct-16 <500 500 40 80
Please help or provide inputs how to calculate PERC% for above details
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Tue, 27 December 2016 08:58] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: Calculate percentage based on month [message #658813 is a reply to message #658809] |
Tue, 27 December 2016 11:21 |
|
amarjadhav
Messages: 40 Registered: April 2011 Location: bangalore
|
Member |
|
|
Below are the sample table creation,insert statements and this table does not have any constraints.
create table test(name varchar2(80 char),month varchar2(15 char),range varchar(15 char),freq_range number(16),count number(16));
DESC TEST;
Name Null Type
------------------------------ -------- --------------
NAME VARCHAR2(80)
MONTH VARCHAR2(15)
RANGE VARCHAR2(15)
FREQ_RANGE NUMBER(16)
COUNT NUMBER(16)
insert into test values('product1','SEP-2016','<500',500,10);
insert into test values('product1','SEP-2016','<1000',1000,30);
insert into test values('product2','SEP-2016','<500',500,20);
insert into test values('product2','SEP-2016','<1000',1000,40);
insert into test values('product1','OCT-2016','<1000',1000,20);
insert into test values('product1','OCT-2016','<500',500,30);
insert into test values('product2','OCT-2016','<1000',1000,10);
insert into test values('product2','OCT-2016','<500',500,40);
select *from test;
NAME MONTH RANGE FREQ_RANGE COUNT
product1 SEP-2016 <500 500 10
product1 SEP-2016 <1000 1000 30
product2 SEP-2016 <500 500 20
product2 SEP-2016 <1000 1000 40
product1 OCT-2016 <1000 1000 20
product1 OCT-2016 <500 500 30
product2 OCT-2016 <1000 1000 10
product2 OCT-2016 <500 500 40
Expected output/result like below with additional column PERC% along with other 5 columns, here i need to calculate percentage based of NAME and MONTH (group by NAME and MONTH).
eg. for SEP-2016 month sum of total "count" for "product1" is "40", percentage column will be (prouct1*100/40)
NAME MONTH RANGE FREQ_RANGE COUNT PERC%
product1 SEP-2016 <500 500 10 25 = 10*100/40 where 40 is sum(count) for month SEP-2016 and NAME "product1"
product1 SEP-2016 <1000 1000 30 75 = 30*100/40
product2 SEP-2016 <500 500 20 33.33333333 = 20*100/60 where 60 is sum(count) for month SEP-2016 and NAME "product2"
product2 SEP-2016 <1000 1000 40 66.66666667 = 40*100/50
product1 OCT-2016 <1000 1000 20 40
product1 OCT-2016 <500 500 30 60
product2 OCT-2016 <1000 1000 10 20
product2 OCT-2016 <500 500 40 80
The above approach is possible though sql or need to write pl sql block,Please help or provide inputs how to calculate PERC% for above details,
|
|
|
|
Re: Calculate percentage based on month [message #658821 is a reply to message #658813] |
Tue, 27 December 2016 22:31 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> select * from test order by month, name, freq_range
2 /
NAME MONTH RANGE FREQ_RANGE COUNT
-------- --------------- --------------- ---------- ----------
product1 OCT-2016 <500 500 30
product1 OCT-2016 <1000 1000 20
product2 OCT-2016 <500 500 40
product2 OCT-2016 <1000 1000 10
product1 SEP-2016 <500 500 10
product1 SEP-2016 <1000 1000 30
product2 SEP-2016 <500 500 20
product2 SEP-2016 <1000 1000 40
8 rows selected.
SCOTT@orcl_12.1.0.2.0> select name, month, range, freq_range, count,
2 count * 100 / sum(count) over (partition by month, name) "PERC%"
3 from test
4 order by to_date (month, 'MON-YYYY'), name, freq_range
5 /
NAME MONTH RANGE FREQ_RANGE COUNT PERC%
-------- --------------- --------------- ---------- ---------- ----------
product1 SEP-2016 <500 500 10 25
product1 SEP-2016 <1000 1000 30 75
product2 SEP-2016 <500 500 20 33.3333333
product2 SEP-2016 <1000 1000 40 66.6666667
product1 OCT-2016 <500 500 30 60
product1 OCT-2016 <1000 1000 20 40
product2 OCT-2016 <500 500 40 80
product2 OCT-2016 <1000 1000 10 20
8 rows selected.
|
|
|
|
Re: Calculate percentage based on month [message #658896 is a reply to message #658854] |
Thu, 29 December 2016 12:29 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You can also use an analytic
select name, month, range, freq_range, count,
ratio_to_report(count) over(partition by month,name) * 100 "PERC%"
from test
order by to_date (month, 'MON-YYYY'), name, freq_range
[Updated on: Thu, 29 December 2016 12:30] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 18:57:30 CDT 2024
|