OraFAQ Forum: SQL & PL/SQL » percentage calculations

Home » SQL & PL/SQL » SQL & PL/SQL » percentage calculations

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

percentage calculations [message #186409]

Mon, 07 August 2006 19:04

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?

Report message to a moderator

Re: percentage calculations [message #186481 is a reply to message #186409]

Tue, 08 August 2006 02:27

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

Report message to a moderator

Re: percentage calculations [message #186483 is a reply to message #186481]

Tue, 08 August 2006 02:30

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

Report message to a moderator

Re: percentage calculations [message #186640 is a reply to message #186483]

Tue, 08 August 2006 16:34

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?

Report message to a moderator

Re: percentage calculations [message #186681 is a reply to message #186640]

Wed, 09 August 2006 00:57

Maaher
Messages: 7065
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 00:45:41 CST 2024