Solved

Calculating the Data in Oracle

Posted on 2011-09-27
11
326 Views
Last Modified: 2012-05-12
I had to calculate varience in oracle. I know there is a function in oracle named varience

I am doing VARIENCE as (MyExpectedData - MyActualReturns)

1. How Does Varience Work...

2. Alo I want to Return data as Percentage for one of the column which is a combination of two columns as:

(Column1/Column2)*100 as myNewData

But this does has errors, if there is a division by zero. It throws an error and second how do i bring its result in percentage sign with it

Please Guide

Thanks
0
Comment
Question by:myselfrandhawa
  • 7
  • 4
11 Comments
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
For getting % symbol you can concatenate with % sign like :

select (((1/1)*100)||'%') percent_data from dual ;


>>> this does has errors, if there is a division by zero.

Can you provide some sample data so that we can check ?
0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
For how variance work ... please find the link below :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions212.htm
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
yes!

sample data like this


select
column1, column2,
(column1/column2 * 100) as column3
from my table

So this is how i am getting the data, But please also tell if there is a divison by zero then how we do handle it
0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
Please find the example as :

create  temp_t (col1 number , col2 number);

begin
insert into temp_t values (1,1);
insert into temp_t values (1,0);
end;

commit;

Error Query:

SQL> select (((col1/col2)* 100 )||'%') data_percent from temp_t ;

Valid Query:

SQL> select  ((CASE WHEN (NVL(max(col2),0) = 0) THEN 0 ELSE ( col1/col2)* 100 END)||'%') percent  
  from temp_t
group by col1,col2;
0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
you can remove the max from the valid query ......
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 500 total points
Comment Utility
Valid Query:

SQL> select  ((CASE WHEN (NVL(col2,0) = 0) THEN 0 ELSE ( col1/col2)* 100 END)||'%') percent  
  from temp_t
group by col1,col2;
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
why using groupby
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
also if omit group it will display correct data rt, also does it handles the decimal places also
0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
...you can remove group by  condition...
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
also will decilams play good
0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
just try it and let us know if you get any issues
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now