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

user_1709270 0
Viewed 230410
SELECT 
    sum(TotalHoursM)
          + (TotalHoursT)
          + (TotalHoursW)
          + (TotalHoursTH)
          + (TotalHoursF) 
          AS TOTAL
FROM LeaveRequest
sql
9 Answers

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.

Mitchel_Sellers_132790

The previous answers using the ISNULL function are correct only for MS Sql Server. The COALESCE function will also work in SQL Server. But will also work in standard SQL database systems. 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. Both work in SQL Server but, COALESCE is ANSI standard and ISNULL is not. Also, 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.

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

You can use ISNULL:

ISNULL(field, VALUEINCASEOFNULL)
user_1196600

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
user_652230

You can also use nvl(Column,0)

user_88612970
Related