Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Smile Unanswered: the problem with isnull

    hi everyone,

    i use isnull function to get a column in decimal data type, and i want to get the sum of it.

    the syntax is as follows:
    SELECT a.acct_no,
    isnull(sum(b.revenue),0)
    FROM table a, table b

    the problem is, whenever there's a single null value in the column revenue, sum value is set to null. I tried to invert the function, i.e., sum(isnull(b.revenue,o)) but it also did the same thing.

    i tried this isnull(b.revenue, 0) and it worked out fine. But what i need is the sum of the revenue column for every acct_no.

    i hope you guys pictured out what i mean. all suggestions or solutions to this would be appreciated.

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bartola
    the problem is, whenever there's a single null value in the column revenue, sum value is set to null
    no, SUM() ignores null

    gotta be some other problem

    do you have any sample rows which demonstrate your situation?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess is that something went mildly south for an entirely different reason. Some SQL variants return NULL when there is a decimal over-run (a nasty habit).

    I'd suggest using the more standard form of:
    PHP Code:
    SELECT a.account
    ,  Sum(b.revenue) -- Will never be NULL unless ALL values are NULL
       FROM tableA 
    AS a
       JOIN tableB 
    AS -- This might need to be a LEFT JOIN
          ON 
    (b.account a.account) -- Is this assumption valid?
       
    GROUP BY a.account -- This seems to be required
       ORDER BY a
    .account -- This is just for convenience 
    Give that a shot and see if it is closer to what you want!

    -PatP

Posting Permissions

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