Sum() statement is not working correctly.

frankyvalley · #1 (**permalink**) 02-19-04, 17:11

if I perform the query:
select stolen, damaged from products where prodid = '343'
it returns 2 fields, one contains 50 and the other contains nothing (null)

If I then perform the querry:
select sum(stolen) + sum(damaged) as lost from products where prodid = '343'

It returns a null value, any ideas why?

is it because the one value is null?

thanks

ss659 · #2 (**permalink**) 02-19-04, 17:43

Quote:

Originally posted by frankyvalley
if I perform the query:
select stolen, damaged from products where prodid = '343'
it returns 2 fields, one contains 50 and the other contains nothing (null)

If I then perform the querry:
select sum(stolen) + sum(damaged) as lost from products where prodid = '343'

It returns a null value, any ideas why?

is it because the one value is null?

thanks

Because null is not a value - It is not like the value 0. So anything + null always evaluates to NULL. If this is on oracle, do something like this:

Code:

Select nvl(sum(stolen),0) + nvl(sum(stolen),0) as lost 
from products
where prodid = '343'

I believe other databases use ISNULL.

Hope this helps.

frankyvalley · #3 (**permalink**) 02-20-04, 10:29

Thats exactly what it was. I was thinking that adding to null was causing a problem but I wasn't too sure how to correct it!

thanks for all your help!!!!!

frankyvalley · #4 (**permalink**) 02-20-04, 11:07

Thats exactly what it was. I was thinking that adding to null was causing a problem but I wasn't too sure how to correct it!

thanks for all your help!!!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech