Joining different tables with the same columns but different data

I have 4 tables all with the same layout.

ID, Name, Totals

Each table may have unique and non-unique IDs if the ID and Name is reused but with different totals. For example;

Year1

Year2

I want to join each table into one query where those with ID that are duplicated to be combined with the Totals field being summed and then Non-unique ID to show their one result.

So the results show as

I can join the tables with a FULL OUTER JOIN which will give me the full results of each table but I don’t know how to create the unique columns with the Totals combined.

Currently the script I am using is as follow to test;

SELECT 
COALESCE (Y1.ID, Y2.ID) AS 'ID', 
COALESCE (Y1.Name, Y2.Name) AS 'Name',
Y1.Totals + Y2.Totals AS 'GrandTotal'
FROM Year1 AS Y1 
FULL OUTER JOIN Y2 AS Y2ON Y1.ID = Y2.PlayerID

This will give me the ID column correctly and Name column correctly but the totals are NULL if the Totals has any NULL instance if that ID doesn’t appear across all tables.

The results I want to see are;

I have also tried;

SELECT 
COALESCE (Y1.ID, Y2.ID) AS 'ID', 
COALESCE (Y1.Name, Y2.Name) AS 'Name',
(SELECT COALESCE(SUM(Y1.Totals),0) FROM Year1) + (SELECT COALESCE(SUM(Y2.Totals),0) FROM Year2)  AS 'GrandTotal'
FROM Year1 AS Y1 
FULL OUTER JOIN Y2 AS Y2ON Y1.ID = Y2.PlayerID

This is initially giving a Column ID is invalid as its not contained in a GROUPBY clause.

When adding a group by clause, it is then not recognizing ID as a column

GROUP BY [ID]

You need to sign in to view this answers