SQLTeam.com | Weblogs | Forums

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

Hi,
I am taking data from two lookup tables and want to take the sum of these two columns. The problem arises when the sum gives NULL values. However, I have used COALESCE to change the lookup values to zero in the below mentioned query.

select t1.[ID]
,t1.[District]
,[CLLG_Interventiond]
,t1.[UC_HH]
,t1.[Benef_Household]
,t1.[Male]
,t1.[Female]
,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 [CLLG].[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

 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