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 Go to next message
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 #658801 is a reply to message #658800] Tue, 27 December 2016 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please note the posted DESC TEST and SELECT * FROM TEST; do NOT match!

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Report message to a moderator

Re: Calculate percentage based on month [message #658804 is a reply to message #658801] Tue, 27 December 2016 09:30 Go to previous messageGo to next message
amarjadhav
Messages: 40
Registered: 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


Please help or provide inputs how to calculate PERC% for above details

Report message to a moderator

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

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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.

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 Go to previous messageGo to next message
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,

Report message to a moderator

Re: Calculate percentage based on month [message #658814 is a reply to message #658813] Tue, 27 December 2016 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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.

Report message to a moderator

Re: Calculate percentage based on month [message #658821 is a reply to message #658813] Tue, 27 December 2016 22:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
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.

Report message to a moderator

Re: Calculate percentage based on month [message #658854 is a reply to message #658821] Wed, 28 December 2016 09:38 Go to previous messageGo to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member
thanks a lot Barbara

Report message to a moderator

Re: Calculate percentage based on month [message #658896 is a reply to message #658854] Thu, 29 December 2016 12:29 Go to previous messageGo to next message
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

Re: Calculate percentage based on month [message #658901 is a reply to message #658896] Thu, 29 December 2016 13:56 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
Nice! I never noticed that one.

Report message to a moderator

Previous Topic: Query table names from dba_tables
Next Topic: Problem accessing external tables over network
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 23:51:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.