Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Show column using sum of two columns Oracle SQL

Posted on 2014-11-13
23
Medium Priority
?
1,388 Views
Last Modified: 2014-11-22
I need to show a column in the results where

  Where the results from this divide    SUM(Case when BALANCE>0 THEN 1 Else 0 end) as "# Parts w/ Bal" -- Assets    (This is a count)      divided into

SUM(Case when BALANCE>0 THEN BALANCE Else 0 end) as "Assets", -- Assets (This is dollar amount)
   

would it look like sum(case when BALANCE>0 THEN 1 Else 0 end) /(Case when BALANCE>0 THEN BALANCE Else 0 end) = "Average"  .  (Dollar amount/ count)= Avg Dollar.  

Do not want to use AVG.   Also need to round to 2 decimal places.



FROM(
      SELECT A.PLAN_ID, PLAN_N,  DVSN_NM, SUM(END_ASET_BAL_A)as BALANCE,  A.AS_OF_D
      FROM dbo.V_SRC_BAL_MO A
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
23 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40440944
The example you posted will likely cause a "divide by 0" error when the balance = 0, correct?  I think you have to careful to avoid that possibility.
0
 

Author Comment

by:leezac
ID: 40440973
Ok - but can you help with that also?

I need to be able to round to two decimals 1231.33 and account for '0's.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40441010
You at least have to tell us what you want returned when the balance is 0.  And, I'm not completely clear on what exactly you want.  Can you provide a few lines of sample data (including at least one line with a balance of 0) plus what you want the results to look like?
0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 

Author Comment

by:leezac
ID: 40441070
Assets                     Partwith Balance      Average Account Balance
$439.00                           9                            $48.85
$805.21                             2                                   $402.60
$1234                                                                          
_____________________________________________________________
If Assest or Part with Balance is either one null Can leave Avg blank.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40441201
When I divide 439 / 9, I get 48.78, not 48.85.  So, that is one question: Is my math correct, or is there some other factor that needs to be included?

Then, in your question, you have this sub-query:
SELECT A.PLAN_ID, PLAN_N,  DVSN_NM, SUM(END_ASET_BAL_A)as BALANCE,  A.AS_OF_D
       FROM dbo.V_SRC_BAL_MO A

But, I don't see the "group by" clause that Oracle requires whenever you use a "group" operator, like: sum, min, max, avg, etc.) and you also select some columns that do not include a "group" operator.

Also, you said that you "Do not want to use AVG".  Can you explain why not?

This part is easy: "need to round to 2 decimal places".  That is simply:
round(...,2)

Replace the "..." with whatever value or expression that you want to have rounded.
0
 

Author Comment

by:leezac
ID: 40441286
Your math is correct and I have a group by at the very end (last row) .  The person requesting this code does not want to use AVG.

GROUP BY PLAN_N,  DVSN_NM

How do I add the Round to the line for the "Average" column to show rounded?

 SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end) as "Average"
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40441340
"The person requesting this code does not want to use AVG."

But, if you sum the BALANCE, then divide by the number of records, how will that be different than letting Oracle calculate the AVG in a query?

This code still does not prevent a "divide by 0" error, if BALANCE = 0:

... /  SUM(Case when BALANCE>0 THEN 1 Else 0 end)

You could add a "where" clause condition to only include records "where BALANCE > 0".  That should avoid the possibility of a "divide by 0" error.

And, dividing by 1 there when the balance is any value greater than 0 also looks incorrect to me.  Dividing by 1 doesn't change anything, so then why divide at all?  I would expect you to divide by the number of records if the number of records > 0 (independent of the BALANCE).
0
 

Author Comment

by:leezac
ID: 40441408
So how would it look if you rewrote it.  I understand your AVG question, but I was asked not to do it.  And I am just doing what I was told. I am also using code previously written and it may not be exactly correct.

I need to see how you would rewrite it like you asked my for examples.   Please.  
I am just getting back using SQL after a long time and need help.  That is why I am posting.  We have used the better part of the day and taken a lot of your time.  

I just need an example of how it should look to give me the results I need.

Include the part about "0".  

If I need to divide by 1 then please show in example.

Thanks
0
 

Author Comment

by:leezac
ID: 40441475
If I use where - I get an error message "missing Right parethesis"

  SUM(Case where BALANCE>0 THEN Balance Else 0 end) as "Assets", -- Assets
0
 

Author Comment

by:leezac
ID: 40441477
Can you please post the solution as it needs to be written without using AVG and adding Round and account for '0'.   I need to see it in an example.  I don't understand what you are saying on how to add round and how to account for '0".

Thank you
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40442518
You have only given us part of your SQL statement.  That makes it very difficult for us to know exactly what data you are working with.

Then, the instruction to not use AVG, but instead use SUM and divide by something is confusing.  It looks to me like that would arrive at the same result as AVG, so why not use AVG?

And, dividing by 1 makes no sense at all.  That doesn't change anything, so there is no reason to divide at all if you will only divide by 1.

No, you can't put a "where" condition inside the case statement.  The "where" condition(s) must be outside of the case statement.  But, until we understand better what the entire data picture looks like, and why a simple AVG is not acceptable, and how the sum and divide should be done to arrive at a different answer than AVG would give, we can't fix just parts of your problem.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40442528
When I asked you for sample data earlier, plus the results you expect, it looks like you posted some results, but no sample detailed data those results are based on.  That makes it difficult for us to know exactly what kind of calculation you are trying to do.  Can you post both some sample detailed data, plus the results you expect?
0
 

Author Comment

by:leezac
ID: 40442590
With the code I posted the results are like 17.3888888 and I need it to so 17.38.  I just need to know how to round it using the code I posted.  That is all.  I need the results to round to 2 decimal places as I have explaned  to show 17.38 as Average.  

I am dividing two number - the first is like 149.45 divided by  2 can be an example, but using the code I have it returns an infinity of decimals when I only need two.  

I can't explan any better.  I am simply asking how to round two decimals using the code I posted.  I understand what you are saying about AVG but that is not what am using.   I understand it is the same.
0
 

Author Comment

by:leezac
ID: 40442646
Dividing 4000.45 by 3 and rounding the result to 7 decimal places  - I need it to round to 2


   SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end) as "Average"
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40442730
Rounding to two decimal places is easy in  Oracle.  That is simply:
round(value,2)
 Just replace "value" with the column name or expression that you want rounded.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40442770
This looks like much more of a problem:
SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end) as "Average"
That second "case" with "Else 0" is certainly a problem, since you are attempting to divide by this.  Also, the first part of that "then 1", makes no sense, because dividing by 1 doesn't change anything.
0
 

Author Comment

by:leezac
ID: 40442772
It is the "Average" column that I need rounding and if I use

SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end) as  round ("Average",2) - i get an error From clause is expected.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40442782
You have to put the entire expression that you want rounded inside the parenthesis for round, like this:
round(case ... end),2)
0
 

Author Comment

by:leezac
ID: 40442789
From my last post is there a way to round numbers showing in the "Average" column which is a result column???
0
 

Author Comment

by:leezac
ID: 40442796
This is what i did and works

  round(SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end),2) as "Average"
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40442803
Yes, if you put the entire expression that you want rounded inside the parenthesis for "round".

But, I'm really concerned/confused by your "case" statement that determines the denominator.  That returns either a 1 , which is meaningless to divide by, or 0 , which will cause a "divide by 0" error.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 40442830
I think you can change this:

round(SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end),2) as "Average"

to simply this:

round(sum(nvl(BALANCE,0)),2) as  "Average"

Explanation:
The division you were doing (by 1) didn't change anything, so there is no reason to do that division.
Maybe there is no data that resulted in a BALANCE = 0, so you never saw the " divide by 0" error.  But writing code that could return a "divide by 0" error is never a good idea.
The "nvl" operator in Oracle  simply substitutes the second parameter (zero, in this case) if the value of BALANCE is null (blank).
1
 

Author Closing Comment

by:leezac
ID: 40459503
Thank you
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

722 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