Home » SQL & PL/SQL » SQL & PL/SQL » Calculate percentage based on month (Oracle 11.2.0.3.0)
Calculate percentage based on month Tue, 27 December 2016 08:56
 amarjadhav Messages: 40Registered: 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
```
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 #658801 is a reply to message #658800] Tue, 27 December 2016 09:01
 BlackSwan Messages: 25848Registered: January 2009 Location: SoCal Senior Member
Please note the posted DESC TEST and SELECT * FROM TEST; do NOT match!

OraFAQ Forum Guide
Re: Calculate percentage based on month [message #658804 is a reply to message #658801] Tue, 27 December 2016 09:30
 amarjadhav Messages: 40Registered: April 2011 Location: bangalore Member
Sorry for the inconvenience, Please find below updated

DESC TEST;
Name Null Type
------------------------------ -------- --------------
NAME VARCHAR2(80)
MONTH VARCHAR2(15)
RANGE VARCHAR2(15)
FREQ_RANGE NUMBER(16)
COUNT 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

Re: Calculate percentage based on month [message #658809 is a reply to message #658804] Tue, 27 December 2016 09:58
 Michel Cadot Messages: 65377Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Calculate percentage based on month [message #658813 is a reply to message #658809] Tue, 27 December 2016 11:21
 amarjadhav Messages: 40Registered: 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 #658814 is a reply to message #658813] Tue, 27 December 2016 11:32
 Michel Cadot Messages: 65377Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Michel Cadot wrote on Tue, 27 December 2016 16:58

Align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: Calculate percentage based on month [message #658821 is a reply to message #658813] Tue, 27 December 2016 22:31
 Barbara Boehmer Messages: 8803Registered: 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 #658854 is a reply to message #658821] Wed, 28 December 2016 09:38
 amarjadhav Messages: 40Registered: April 2011 Location: bangalore Member
thanks a lot Barbara
Re: Calculate percentage based on month [message #658896 is a reply to message #658854] Thu, 29 December 2016 12:29
 Bill B Messages: 1715Registered: 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

Re: Calculate percentage based on month [message #658901 is a reply to message #658896] Thu, 29 December 2016 13:56
 Barbara Boehmer Messages: 8803Registered: November 2002 Location: California, USA Senior Member
Nice! I never noticed that one.
 Previous Topic: Query table names from dba_tables Next Topic: Problem accessing external tables over network
Goto Forum:

Current Time: Sat Feb 17 17:08:24 CST 2018

Total time taken to generate the page: 0.01143 seconds