SQL: sum 3 columns when one column has a null value?

Question

SELECT 
    sum(TotalHoursM)
          + (TotalHoursT)
          + (TotalHoursW)
          + (TotalHoursTH)
          + (TotalHoursF) 
          AS TOTAL
FROM LeaveRequest

Answer 1

If the column has a 0 value, you are fine, my guess is that you have a problem with a Null value, in that case you would need to use IsNull(Column, 0) to ensure it is always 0 at minimum.

Answer 2

You can use ISNULL:

ISNULL(field, VALUEINCASEOFNULL)

Answer 3

SELECT sum(isnull(TotalHoursM,0)) 
         + isnull(TotalHoursT,0) 
         + isnull(TotalHoursW,0) 
         + isnull(TotalHoursTH,0) 
         + isnull(TotalHoursF,0) 
AS TOTAL FROM LeaveRequest

Answer 4

looks like you want to SUM all the columns (I'm not sure where "sum 3 columns" comes from), not just TotalHoursM, so try this:

SELECT 
    SUM(    ISNULL(TotalHoursM  ,0)
          + ISNULL(TotalHoursT  ,0)
          + ISNULL(TotalHoursW  ,0)
          + ISNULL(TotalHoursTH ,0)
          + ISNULL(TotalHoursF  ,0) 
       ) AS TOTAL
    FROM LeaveRequest

Answer 5

The previous answers using the ISNULL function are correct. The COALESCE function will also work. In the given example:

SELECT sum(COALESCE(TotalHoursM,0)) + COALESCE(TotalHoursT,0) + COALESCE(TotalHoursW,0) + COALESCE(TotalHoursTH,0) + COALESCE(TotalHoursF,0) AS TOTAL FROM LeaveRequest

This is identical to the ISNULL solution with the only difference being the name of the function. The is some disagreement in the SQL world about which is 'better'. But both work. I submit this alternative for two reasons. The COALESCE is ANSI standard and ISNULL is not. But, more important is the fact that COALESCE is more flexible. ISNULL will only work with two parameters. If the first parameter is NULL then the value of the second parameter is returned, else the value of the first is returned. COALESCE will take 2 to 'n' (I don't know the limit of 'n') parameters and return the value of the first parameter that is not NULL. When there are only two parameters the effect is the same as ISNULL.