The revolutionary project management tool is here! Plan visually with a single glance and make sure your projects get done.

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

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

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

$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.

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.

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"

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).

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

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

Thank you

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.

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.

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

round(value,2)

Just replace "value" with the column name or expression that you want rounded.

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.

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.

round(case ... end),2)

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

All Courses

From novice to tech pro — start learning today.

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)),

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).