• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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
0
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 7
  • 4
1 Solution
 
Swadhin RaySenior Technical Engineer Commented:
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
 
Swadhin RaySenior Technical Engineer Commented:
For how variance work ... please find the link below :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions212.htm
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Swadhin RaySenior Technical Engineer Commented:
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
 
Swadhin RaySenior Technical Engineer Commented:
you can remove the max from the valid query ......
0
 
Swadhin RaySenior Technical Engineer Commented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
why using groupby
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
also if omit group it will display correct data rt, also does it handles the decimal places also
0
 
Swadhin RaySenior Technical Engineer Commented:
...you can remove group by  condition...
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
also will decilams play good
0
 
Swadhin RaySenior Technical Engineer Commented:
just try it and let us know if you get any issues
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now