How to get a sum of three columns row wise from three different tables in SQL Server?

Question

I have three tables A, B and C which have an Amount column in them as shown in the screenshot. I want the output to be the sum of the Amount column from all the three tables as shown.

I tried with all joins but the conditions are not matching.

Can anyone shed some light on this issue?

Answer 1

Instead of joins you should use UNION ALL to get all the rows of the 3 tables and then aggregate:

WITH cte AS (
  SELECT RP, Row, Amount FROM TableA
  UNION ALL
  SELECT RP, Row, Amount FROM TableB
  UNION ALL
  SELECT RP, Row, Amount FROM TableC
)
SELECT RP, Row, SUM(Amount) AS Amount
FROM cte
GROUP BY RP, Row;