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
ID | Name | Totals |
---|---|---|
1 | Bob | 15 |
2 | John | 2 |
3 | Smith | 4 |
4 | Carl | 4 |
Year2
ID | Name | Totals |
---|---|---|
1 | Bob | 10 |
3 | Smith | 2 |
4 | Carl | 1 |
5 | Greg | 10 |
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
ID | Name | Totals |
---|---|---|
1 | Bob | 25 |
2 | John | 2 |
3 | Smith | 6 |
4 | Carl | 5 |
5 | Greg | 10 |
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.
ID | Name | GrandTotals |
---|---|---|
1 | Bob | 25 |
2 | John | NULL |
3 | Smith | 6 |
4 | Carl | 5 |
5 | Greg | NULL |
The results I want to see are;
ID | Name | Totals |
---|---|---|
1 | Bob | 25 |
2 | John | 2 |
3 | Smith | 6 |
4 | Carl | 5 |
5 | Greg | 10 |
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