asked on

Calculating the Data in Oracle

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

Swadhin Ray

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 ?

Swadhin Ray

For how variance work ... please find the link below :

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

Coast Line

ASKER

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

Swadhin Ray

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;

Swadhin Ray

you can remove the max from the valid query ......

ASKER CERTIFIED SOLUTION

Swadhin Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Coast Line

ASKER

why using groupby

Coast Line