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

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?

CodePudding user response：

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;

CodePudding user response：

To get the sum of three columns row-wise from three different tables in SQL Server, you can use a combination of the UNION ALL operator and the SUM function.

Here's an example of how you could do this:

SELECT t1.col1   t2.col2   t3.col3 AS total_sum
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id

UNION ALL

SELECT t1.col1   t2.col2   t3.col3 AS total_sum
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id

This will return a result set with one column, total_sum, which contains the sum of the values in col1, col2, and col3 for each row in the three tables.

Note that you can use the JOIN operator to join the three tables based on a common column (such as id in this example). You can also use other types of JOINs, such as INNER JOIN or LEFT JOIN, depending on your requirements.

You can also use the GROUP BY clause to group the results by a specific column or set of columns. For example:

SELECT t1.col1, t2.col2, t3.col3, SUM(t1.col1   t2.col2   t3.col3) AS 
total_sum
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id
GROUP BY t1.col1, t2.col2, t3.col3

This will return a result set with four columns: col1, col2, col3, and total_sum, where total_sum is the sum of the values in col1, col2, and col3 for each unique combination of values in these columns.