Solved

# Show column using sum of two columns Oracle SQL

Posted on 2014-11-13
663 Views
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
Question by:leezac
• 12
• 11

LVL 34

Expert Comment

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

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 34

Expert Comment

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

Author Comment

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 34

Expert Comment

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

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 34

Expert Comment

"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

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.

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

Thanks
0

Author Comment

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

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 34

Expert Comment

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 34

Expert Comment

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

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

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 34

Expert Comment

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 34

Expert Comment

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

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 34

Expert Comment

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

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

Author Comment

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 34

Expert Comment

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 34

Accepted Solution

Mark Geerlings earned 500 total points
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).
0

Author Closing Comment

Thank you
0

## Featured Post

### Suggested Solutions

SQL syntax error in VBA 11 31
Passing value to a stored procedure 8 66
T-SQL Convert to PL/SQL 23 59
PL/SQL - Leading zeros 7 39
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-orâ€¦
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.