SQL: 当一列有空值时，对3列求和？

小开

最佳答案

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.

小开

You can use ISNULL:

ISNULL(field, VALUEINCASEOFNULL)

小开

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

小开

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

小开

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.

小开

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

小开

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.

小开

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

小开

You can also use nvl(Column,0)