SQL query to find out some of three fields from three different table

Question

I have three tables, tableA (id, A), tableB (id,B) and tableC (id,C). id is unique and primary key. Now I want to run a query on these three tables to find out sum of values of A,B and C for every id. (i.e. if id 1 is present in tableA but not in tableB then value B should be considered as 0 for id 1).example: tableA:

id  A
1   5
2   6
3   2
5   7

tableB:

id  B
2   5
3   8
4   1

tableC:

id  C
5   2

the output should be:

id  Sum
1   (5 + 0 + 0 =)5
2   (6 + 5 + 0 =)11
3   (2 + 8 + 0 =)10
4   (0 + 1 + 0 =)1
5   (7 + 0 + 2 =)9

Answer 1

First get a distinct list ( UNION ) of the IDs so that you include all, then LEFT JOIN to add the values together.

Something like

SELECT  IDs.ID,
         IFNULL(tableA.A,0) + IFNULL(tableB.B,0) + IFNULL(tableC.C,0) SumVal
FROM    (
            SELECT  ID
            FROM    tableA
            UNION
            SELECT  ID
            FROM    tableB
            UNION
            SELECT  ID
            FROM    tableC
        ) IDs LEFT JOIN
        tableA ON IDs.ID = tableA.ID LEFT JOIN
        tableB ON IDs.ID = tableB.ID LEFT JOIN
        tableC ON IDs.ID = tableC.ID

Answer 2

Something like this should work:

select id, sum(val) from
( select id, "A" as val from "tableA"
  union all
  select id, "B" as val from "tableB"
  union all
  select id, "C" as val from "tableC" ) as joined
group by id
order by id

Answer 3

I could not test it with MySql but this works my databases (HSQLDB, Oracle):

select ID, sum(X) from 
  (SELECT  ID, A as X FROM    tableA
   UNION
   SELECT  ID, B as X FROM    tableB
   UNION
   SELECT  ID, C as X FROM    tableC)
group by ID

Answer 4

Not sure about the exact MySQL syntax, but this works in SQL Server:

SELECT ID, SUM(ColToSum) As SumValue FROM
(
SELECT ID, A As ColToSum FROM TableA
UNION ALL
SELECT ID, B As ColToSum FROM TableB
UNION ALL
SELECT ID, C As ColToSum FROM TableC
) Combined
GROUP BY ID

Remember to use "UNION ALL", not just "UNION" which strips out duplicate rows as it combines (see http://dev.mysql.com/doc/refman/5.0/en/union.html)