Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Sum() statement is not working correctly.

    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

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Sum() statement is not working correctly.

    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    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!!!!!

  4. #4
    Join Date
    Feb 2004
    Posts
    5
    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!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •