How to add another same column from three or more tables in query?

Question

SELECT 
    material, 
    SUM(IIF(source = 'table1', sale, 0)) AS table1_sale, 
    SUM(IIF(source = 'table2', sale, 0)) AS table2_sale,
    SUM(IIF(source = 'table3', sale, 0)) AS table3_sale
FROM
    (SELECT material, sale, 'table1' AS source 
     FROM table1 
     UNION ALL
     SELECT material, sale, 'table2'
     FROM table2
     UNION ALL
     SELECT material, sale, 'table3'
     FROM table3) x
GROUP BY 
    material
ORDER BY 
    material;

This code is my answer, but I want show another same column of tables into the query table please help me

Answer 1

You can add the totals column with SUM(sale) AS Total.

For the id column you must take care to add it to all the SELECT statements and also to the GROUP BY clause.

SELECT
    id,
    material, 
    SUM(IIF(source = 'table1', sale, 0)) AS table1_sale, 
    SUM(IIF(source = 'table2', sale, 0)) AS table2_sale,
    SUM(IIF(source = 'table3', sale, 0)) AS table3_sale,
    SUM(sale) AS Total
FROM
    (SELECT id, material, sale, 'table1' AS source
     FROM table1 
     UNION ALL
     SELECT id, material, sale, 'table2'
     FROM table2
     UNION ALL
     SELECT id, material, sale, 'table3'
     FROM table3) x
GROUP BY 
    id, material
ORDER BY 
    material;

Alternatively, you can select the 3 sales where each one of them returns the sale in turn for each table and the other two return 0.0.

SELECT
    id,
    material, 
    SUM(sale1) AS table1_sale,
    SUM(sale2) AS table2_sale,
    SUM(sale3) AS table3_sale,
    SUM(sale1 + sale2 + sale3) AS Total
FROM
    (SELECT id, material, sale AS sale1, 0.0  AS sale2, 0.0  AS sale3
     FROM table1 
     UNION ALL
     SELECT id, material, 0.0  AS sale1, sale AS sale2, 0.0  AS sale3
     FROM table2
     UNION ALL
     SELECT id, material, 0.0  AS sale1, 0.0  AS sale2, sale AS sale3
     FROM table3) x
GROUP BY 
    id, material
ORDER BY 
    material;