Sum of 2 columns with values returns zero

Hi all,
This is part from a stored procedure..
Update mwebtemp
Set Temp_amount2= (SELECT nvl(SUM((NVL(Sum(Sched_Amount),0.0))+ (NVL(Temp_Amount1,0.0))),0)
FROM mwebSchedule
WHERE Sched_Work_ID in (select Work_ID from mwebWork where Work_Entity_Type in(4, 30)
and Work_Int_Ext7 = 10 and Work_Int_Ext8 > 0)
and Sched_Res_ID=Temp_Ent_ID and Sched_Category = 4500
AND Sched_Date BETWEEN dtstartdate AND dtenddate
and to_char(Sched_Date, 'D') between 2 and 6 GROUP BY Temp_Amount1 )
Where Temp_Trancode = inTrancode;

In this statement(above) if the sum(sched_amount) is null or 0 the whole select statement returns 0, even though temp_amount1 has value. In other words, if sum(sched_amount) = 0 or null and temp_amount1<>0 ( has some value say 15) then temp_amount2 gets value 0 and not 15. Why ?
I just can't figure out why this is happening

Update mwebtemp
Set Temp_amount2= nVL(Temp_Amount1,0)
WHERE Temp_Trancode = inTrancode
and temp_amount2=0;

Then I wrote this statement followed by 1st statement and now it gets the value when sum(sched_amount)= 0.

I tried placing SUM and NVL in different places.. some crazy tries but nothing seems to work.


thanks a lot

Sonali

You don't need to use NVL with SUM fumction. Just say SUM. The query will return correct value.

I tried this it still doesn't work..

Update mwebtemp
Set Temp_amount2= (SELECT Sum(Sched_Amount)+ (NVL(Temp_Amount1,0.0))
FROM mwebSchedule
WHERE Sched_Work_ID in (select Work_ID from mwebWork where Work_Entity_Type in(4, 30)
)
and Sched_Res_ID=Temp_Ent_ID and Sched_Category = 4500
GROUP BY Temp_Amount1 )
Where Temp_Trancode = inTrancode;

I took out some portion of where clause to make it simpler..

I also tried...this one doesn't work either.. What is wrong in my query ?


Update mwebtemp
Set Temp_amount2= (SELECT SUM(Sum(Sched_Amount)+ (NVL(Temp_Amount1,0.0)))
FROM mwebSchedule
WHERE Sched_Work_ID in (select Work_ID from mwebWork where Work_Entity_Type in(4, 30)
)
and Sched_Res_ID=Temp_Ent_ID and Sched_Category = 4500
GROUP BY Temp_Amount1 )
Where Temp_Trancode = inTrancode;

I have one more question
How do you Sum and NVL in select statement ?
Do you do
NVL(Sum(col1),0)

OR do you do
sum(nvl(col1,0))

Which one of above is correct in the earlier query I have mentioned ?

thanks

Both are correct in a sence that they will both return you the same result. But NVL(Sum(col1),0) will require less CPU than SUM(NVL(col1,0)), so it will be quicker.

SUM ignores the NULL values and only sums the non-null values. But if all the values to be summed are NULL then it will return NULL. So only in this case will the NVL function be executed if you use NVL(Sum(col1),0) to return you a zero, and it will be executed only once. But if you use SUM(NVL(col1,0)) then NVL will be applied to all NULL values before they will be added to the sum, but this is useless as the sum will not change if you add 0.