Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: select sum() returns null

    hi,

    i have the following query to sum the total due balance for a customer:

    select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?

    thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    isnull

  3. #3
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63

    Re: select sum() returns null

    Hi,

    you just use the COALESCE command like this:

    select COALESCE(sum(outstanding), 0) from out where customer = 'myvariable'

    Greetings,
    Carsten

    Originally posted by caisys
    hi,

    i have the following query to sum the total due balance for a customer:

    select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?

    thanks

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    select isnull(sum(outstanding),0) from out where customer = 'myvariable'
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jun 2012
    Posts
    4

    Re: select sum() returns null

    Hi,

    To avoid null columns you can use below query.

    select sum(nvl(outstanding,0)) from customers;

    The "nvl" is a function which replaces the value with 0 if it is null.

    Regards,
    Khan

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    mahmoodkhan,

    Is NVL() a T-SQL function?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PracticalProgram View Post
    Is NVL() a T-SQL function?
    obviously not

    and just as obviously, mahmoodkhan neglected to read the thread, where he would ahve seen that the correct answer, COALESCE, was laready given

    but even more disturbing is the fact that mahmoodkhan was attempting to answer a thread that is over eight years old

    mahmoodkhan, a suggestion for your future posts -- read what you're replying to, and if it's not a current thread, don't bother
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I've said this before . . .

    . . . the site should lock all threads older than, say six months.

    If someone, for some legitimate reason, wants to comment-on, or refer-to, an old thread, the writer can simply copy the URL of the old thread into their new thread.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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