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

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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.

Solution 2 - Sql

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.

Solution 3 - Sql

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

Solution 4 - Sql

Just for reference, the equivalent statement for MySQL is: IFNull(Column,0).

This statement evaluates as the column value if not null, otherwise it is evaluated as 0.

Solution 5 - Sql

You can use ISNULL:

ISNULL(field, VALUEINCASEOFNULL)

Solution 6 - Sql

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

Solution 7 - Sql

You can also use nvl(Column,0)

Solution 8 - Sql

I would try this:

select sum (case when TotalHousM is null then 0 else TotalHousM end)
       + (case when TotalHousT is null then 0 else TotalHousT end)
       + (case when TotalHousW is null then 0 else TotalHousW end)
       + (case when TotalHousTH is null then 0 else TotalHousTH end)
       + (case when TotalHousF is null then 0 else TotalHousF end)
       as Total
From LeaveRequest

Solution 9 - Sql

If you want to avoid the null value use IsNull(Column, 1)

Content Type	Original Author	Original Content on Stackoverflow
Question	Yves	View Question on Stackoverflow
Solution 1 - Sql	Mitchel Sellers	View Answer on Stackoverflow
Solution 2 - Sql	Jim Reineri	View Answer on Stackoverflow
Solution 3 - Sql	gjutras	View Answer on Stackoverflow
Solution 4 - Sql	omkarv	View Answer on Stackoverflow
Solution 5 - Sql	Colin	View Answer on Stackoverflow
Solution 6 - Sql	KM.	View Answer on Stackoverflow
Solution 7 - Sql	David	View Answer on Stackoverflow
Solution 8 - Sql	Jerome	View Answer on Stackoverflow
Solution 9 - Sql	Mohamed Omer	View Answer on Stackoverflow