SQL sum 3 columns when one column has a null value

Question

SELECT      sum TotalHoursM               TotalHoursT               TotalHoursW               TotalHoursTH               TotalHoursF             AS TOTAL FROM LeaveRequest

User · Accepted Answer

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

User · Answer

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

User · Answer

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 · Answer

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 · Answer

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

User · Answer

You can also use nvl Column 0

User · Answer

You can use ISNULL   ISNULL field  VALUEINCASEOFNULL

User · Answer

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

User · Answer

SELECT sum isnull TotalHoursM 0               isnull TotalHoursT 0              isnull TotalHoursW 0              isnull TotalHoursTH 0              isnull TotalHoursF 0   AS TOTAL FROM LeaveRequest

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

Examples related to sql