Adding SELECT values with a NULL

• Magy

  SSCarpal Tunnel

  Points: 4217

  More actions

  January 25, 2007 at 6:25 am

  #122744

  I just found out today that adding a NULL to any value results in a NULL.

  I have a SELECT statement that looks like this:

  SELECT WCAmount + WCHours AS Total FROM worktime WHERE WCid = 'WC11111'

  Now if either WCAmount or WCHours is NULL, then I get NULL back.

  Is there a way to still add the two columns together, and if one happens to be NULL, still get the value of the column that isn't NULL?

  Kind of like if NULL was 0, it would be 4+0...I will still get a 4 back even if the other value was 0. I still need to return one column's results even if the other is NULL. Now if they are both NULL, then getting a NULL back is fine.

  Any ideas?

  Thanks!

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 25, 2007 at 6:32 am

  #685501

  Basic stuff...

  SELECT COALESCE( WCAmount, 0 ) + COALESCE( WCHours, 0 ) AS Total FROM worktime

  WHERE WCid = 'WC11111'

   

  OR

   

  SELECT ISNULL( WCAmount, 0  ) + ISNULL( WCHours, 0 ) AS Total FROM worktime

  WHERE WCid = 'WC11111'

  --Ramesh
  

  ---