5

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
0

3 Answers 3

13

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
1
  • 1
    My problem solved-Thank you very much for the support Aug 8 at 5:22
7

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

2
  • My problem solved-Thank you very much for the support Aug 8 at 5:22
  • Don't forget to mark this or one of the other answers as the correct answer. It will help feature readers to quickly find a solution if they have a similar påroblem. You do this by clicking on the check box at the top left corner.
    – Lennart
    Aug 8 at 7:43
0

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;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.