Home » SQL & PL/SQL » SQL & PL/SQL » percentage calculations
percentage calculations [message #186409] Mon, 07 August 2006 19:04 Go to next message
gopal2005
Messages: 9
Registered: October 2005
Location: Detroit
Junior Member
I am herewith attaching the scripts and inserts. I am trying to get the
percentages based on the year as well as the term but for now just based on the
year.

CREATE TABLE MYTAB (ID NUMBER(4),
MYYR VARCHAR2(4),
MYTERM VARCHAR2(7),
MYTYPE VARCHAR2(10),
MYDUP NUMBER(6),
MYCT NUMBER(6));


INSERT INTO MYTAB values (12,'2001','2001/2','Credit',1234,2345);
INSERT INTO MYTAB values (13,'2002','2002/2','Credit',1345,3456);
INSERT INTO MYTAB values (14,'2003','2003/2','Credit',2345,4567);
INSERT INTO MYTAB values (15,'2004','2004/2','Credit',3456,5678);
INSERT INTO MYTAB values (16,'2005','2005/2','Credit',4567,6789);
INSERT INTO MYTAB values (17,'2006','2006/2','Credit',5678,7890);

COMMIT;

select * from MYTAB;


ID|MYYR|MYTERM|MYTYPE|MYDUP|MYCT
12|2001|2001/2|Credit|1234|2345
13|2002|2002/2|Credit|1345|3456
14|2003|2003/2|Credit|2345|4567
15|2004|2004/2|Credit|3456|5678
16|2005|2005/2|Credit|4567|6789
17|2006|2006/2|Credit|5678|7890


I want to get the percentage based on 2001 and 2002, 2001 and 2003, 2001 and
2004 and so on

Like for example:

If it is 2002 then it should be 100*((MYDUP of 2002) - MYDUP of 2001))/(MYDUP of
2002)=100*(1345-1234)/1345=8.25
if it is 2003 then it should be 100*((MYDUP of 2003) - MYDUP of 2001))/(MYDUP of
2003)=100*(2345-1234)/2345=47.38
If it is 2004 then it should be 100*((MYDUP of 2004) - MYDUP of 2001))/(MYDUP of
2004)=100*(3456-1234)/3456=64.29

and so on...

Also need to have the percentages with consecutive years as well ....

How do we get the results using the analytical functions or any built in
functions?


Re: percentage calculations [message #186481 is a reply to message #186409] Tue, 08 August 2006 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thank you very much for providing CREATE TABLE and INSERT statements - it makes things a lot easier.

This query provides the answers you asked for - I'll see if I can come up with some other ways of doing it as well, but if I don't I'm sure others will.

SQL> select mt.myyr
  2        ,round(100*(mt.mydup - mt2001.mydup)/mt.mydup,2) pct
  3  from   mytab mt
  4        ,(select * from mytab where myyr = '2001') mt2001;

MYYR|       PCT
----|----------
2001|         0
2002|      8.25
2003|     47.38
2004|     64.29
2005|     72.98
2006|     78.27
Re: percentage calculations [message #186483 is a reply to message #186481] Tue, 08 August 2006 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's another way of doing it:

SQL> select myyr
  2        ,round(100*(mydup - mydup_2001)/mydup,2) pct
  3  from  (select mt.mydup
  4               ,mt.myyr
  5               ,first_value(mt.mydup) over (order by myyr) mydup_2001
  6         from   mytab mt);

MYYR|       PCT
----|----------
2001|         0
2002|      8.25
2003|     47.38
2004|     64.29
2005|     72.98
2006|     78.27
Re: percentage calculations [message #186640 is a reply to message #186483] Tue, 08 August 2006 16:34 Go to previous messageGo to next message
gopal2005
Messages: 9
Registered: October 2005
Location: Detroit
Junior Member
Thank you very much for your response. It helped. But what if I want to see the percentages based on second, third or fourth years. The result gives only for the first value based on the year that is (2002-2001), (2003-2001), (2004,2001) and so on. What if I want to have percentage of (2005-2004) value or (2004-2003) value?
Re: percentage calculations [message #186681 is a reply to message #186640] Wed, 09 August 2006 00:57 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The clue was given by JRowbottom. The rest is just tweaking in the query below, all percentage differences are given:
SELECT a.myyr base
     , b.myyr year
     , round(100*(a.mydup-b.mydup)/a.mydup,2) pct
FROM   mytab a
   ,   mytab b
WHERE  a.myyr >= b.myyr
/
It should give the following result:
BASE YEAR        PCT
---- ---- ----------
2001 2001          0
2002 2001       8.25
2003 2001      47.38
2004 2001      64.29
2005 2001      72.98
2006 2001      78.27
2002 2002          0
2003 2002      42.64
2004 2002      61.08
2005 2002      70.55
2006 2002      76.31

BASE YEAR        PCT
---- ---- ----------
2003 2003          0
2004 2003      32.15
2005 2003      48.65
2006 2003       58.7
2004 2004          0
2005 2004      24.33
2006 2004      39.13
2005 2005          0
2006 2005      19.57
2006 2006          0

21 rows selected.
All you need to do now if you want a particular year to be the reference is to add some where clauses.

MHE
Previous Topic: Date difference in sql
Next Topic: MAIL SENDING THROUGH PL/SQL
Goto Forum:
  


Current Time: Sat Dec 10 16:36:40 CST 2016

Total time taken to generate the page: 0.09510 seconds