SQL: sum 3 columns when one column has a null value?
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.
SELECT sum(isnull(TotalHoursM,0))
+ isnull(TotalHoursT,0)
+ isnull(TotalHoursW,0)
+ isnull(TotalHoursTH,0)
+ isnull(TotalHoursF,0))
AS TOTAL FROM LeaveRequest
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
.
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.