Solved

Calculating the Data in Oracle

Posted on 2011-09-27
11
328 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
  • 7
  • 4
11 Comments
 
LVL 16

Expert Comment

by:Swadhin Ray
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 16

Expert Comment

by:Swadhin Ray
ID: 36715082
For how variance work ... please find the link below :

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

Author Comment

by:Gurpreet Singh Randhawa
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Expert Comment

by:Swadhin Ray
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 16

Expert Comment

by:Swadhin Ray
ID: 36715700
you can remove the max from the valid query ......
0
 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 500 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

by:Gurpreet Singh Randhawa
ID: 36716031
why using groupby
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36716047
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
ID: 36716057
...you can remove group by  condition...
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36716118
also will decilams play good
0
 
LVL 16

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error when loading the database 16 64
append to an ms access field 6 33
PL SQL Search Across Columns 4 35
Mongo DB 18 35
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

813 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

10 Experts available now in Live!

Get 1:1 Help Now