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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
leezac

8/22/2022 - Mon
Mark Geerlings

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.
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.
Mark Geerlings

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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.
Mark Geerlings

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.
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"
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mark Geerlings

"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).
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
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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
Mark Geerlings

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.
Mark Geerlings

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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"
Mark Geerlings

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Geerlings

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.
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.
Mark Geerlings

You have to put the entire expression that you want rounded inside the parenthesis for round, like this:
round(case ... end),2)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
leezac

ASKER
From my last post is there a way to round numbers showing in the "Average" column which is a result column???
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"
Mark Geerlings

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Mark Geerlings

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
leezac

ASKER
Thank you