Link to home
Start Free TrialLog in
Avatar of leezac
leezac

asked on

Show column using sum of two columns Oracle SQL

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
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

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.
Avatar of leezac
leezac

ASKER

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.
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?
Avatar of leezac

ASKER

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.
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.
Avatar of leezac

ASKER

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"
"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).
Avatar of leezac

ASKER

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
Avatar of leezac

ASKER

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
Avatar of leezac

ASKER

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
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.
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?
Avatar of leezac

ASKER

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.
Avatar of leezac

ASKER

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"
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.
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.
Avatar of leezac

ASKER

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.
You have to put the entire expression that you want rounded inside the parenthesis for round, like this:
round(case ... end),2)
Avatar of leezac

ASKER

From my last post is there a way to round numbers showing in the "Average" column which is a result column???
Avatar of leezac

ASKER

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"
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.
ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of leezac

ASKER

Thank you