x
Solved

# Calculating the Data in Oracle

Posted on 2011-09-27
Medium Priority
339 Views
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

Thanks
0
• 7
• 4

LVL 17

Expert Comment

ID: 36715069
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 17

Expert Comment

ID: 36715082

0

LVL 16

Author Comment

ID: 36715508
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 17

Expert Comment

ID: 36715655
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 17

Expert Comment

ID: 36715700
you can remove the max from the valid query ......
0

LVL 17

Accepted Solution

Swadhin Ray earned 2000 total points
ID: 36715723
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 16

Author Comment

ID: 36716031
why using groupby
0

LVL 16

Author Comment

ID: 36716047
also if omit group it will display correct data rt, also does it handles the decimal places also
0

LVL 17

Expert Comment

ID: 36716057
...you can remove group by  condition...
0

LVL 16

Author Comment

ID: 36716118
also will decilams play good
0

LVL 17

Expert Comment

ID: 36716218
just try it and let us know if you get any issues
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
A method of moving multiple mailboxes (in bulk) to another database in an Exchange 2010/2013/2016 environment...
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
###### Suggested Courses
Course of the Month4 days, 20 hours left to enroll