NULL values appear when taking the sum of two columns taken from Lookup tables

Question

I am using SQL Server and taking data from two lookup tables and want to take the sum of these two columns.

The problem arises when the sum of these two columns (lookup values) gives NULL values. However, I have used COALESCE to change the NULL values to zero.

The line I am calculating the sum is this one: (t2.[Score]+t3.[Score]) as Total

select  t1.[ID]
      ,t1.[District]
      ,[CLLG_Interventiond]
      ,t1.[UC_HH]
      ,t1.[Benef_Household]
      ,t1.[Duration_Years]
      ,t1.[Duration_Months]
      ,t1.[Budget_PKR]
      ,t1.[Priority]
      ,COALESCE(t2.[Score],0) as E_Score
      ,COALESCE(t3.[Score],0) as UCDP_Score
      ,(t2.[Score]+t3.[Score]) as Total
  FROM [dbo].[Data] as t1
  left outer join E_Growth as t2
  on t1.CLLG_Interventiond = t2.Intervention
  left outer join UCDP as t3
 on  t1.Priority = t3.Priority

Answer 1

Try to use COALESCE for the values in the SUM Like this:

COALESCE(t2.[Score],0) + COALESCE(t3.[Score],0) as Total

The query will look like this:

select  t1.[ID]
      ,t1.[District]
      ,[CLLG_Interventiond]
      ,t1.[UC_HH]
      ,t1.[Benef_Household]
      ,t1.[Duration_Years]
      ,t1.[Duration_Months]
      ,t1.[Budget_PKR]
      ,t1.[Priority]
      ,COALESCE(t2.[Score],0) as E_Score
      ,COALESCE(t3.[Score],0) as UCDP_Score
      ,COALESCE(t2.[Score],0) + COALESCE(t3.[Score],0) as Total
  FROM [dbo].[Data] as t1
  left outer join E_Growth as t2
  on t1.CLLG_Interventiond = t2.Intervention
  left outer join UCDP as t3
 on  t1.Priority = t3.Priority

Answer 2

Since you already handled that, you can nest your query and calculate the total at the outer level:

SELECT T.*, T.E_SCORE + T.UCDP_SCORE AS total
FROM (
    select  t1.[ID]
           ,t1.[District]
           ,[CLLG_Interventiond]
           ,t1.[UC_HH]
           ,t1.[Benef_Household]
           ,t1.[Duration_Years]
           ,t1.[Duration_Months]
           ,t1.[Budget_PKR]
           ,t1.[Priority]
           ,COALESCE(t2.[Score],0) as E_Score
           ,COALESCE(t3.[Score],0) as UCDP_Score

   FROM [dbo].[Data] as t1
   left outer join E_Growth as t2
      on t1.CLLG_Interventiond = t2.Intervention
   left outer join UCDP as t3
      on  t1.Priority = t3.Priority
) as T

A CTE could have been used in the same way

Answer 3

Using CTE with IIF:

Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server. e.g IIF( boolean_expression, true_value, false_value )

WITH CTE AS
(
select  t1.[ID],
-- other columns...
,IIF(t2.[Score] IS NOT NULL, t2.[Score], 0) E_Score
,IIF(t3.[Score] IS NOT NULL, t3.[Score], 0) UCDP_Score
-- conditions of joining...
)
SELECT T.*, T.E_SCORE + T.UCDP_SCORE AS total FROM CTE as T;