MySQL Database Tables connect Help (JOIN / SUM)

1

Hey Guys! I'm completely new to SQL. I have to Tables, Table1 and Table2.

Inside of them are data from different soccer players. Both have the exact same structure. For Example, Player A shot in Season 1 (Table1) 5 goals. In Season 2 (Table2) he shot 3 Goals.

I want to have a third table now (Table0), ich which all data is coming together. So in my Example, in Table0 Player A should have 8 Goals.

I have many Players and of course other statistics like passes and so on (all data should be added together (except the player name and the player ID, both are always same). But I'm not able to create Table0 the way I want, due to my lack of skills.

Hopefully one of you can help me out! :slight_smile:

Thanks a lot in advance!

2

I think this will work in MySQL, although not 100% sure. It would work fine in SQL Server :grin:

INSERT INTO Table0
SELECT
    COALESCE(t1.PlayerId, t2.PlayerId) AS PlayerId,
    COALESCE(t1.PlayerName, t2.PlayerName) AS PlayerName,
    COALESCE(t1.goals, 0) + ISNULL(t2.goals, 0) AS goals,
    COALESCE(t1.<other_column>, 0) + ISNULL(t2.<other_column>, 0) AS <other_column>,
    ...

FROM (
    SELECT PlayerId, PlayerName, SUM(goals) AS goals /*, SUM(...) AS ...,*/
    FROM Table1
    GROUP BY PlayerId, PlayerName
) AS t1
FULL OUTER JOIN (
    SELECT PlayerId, PlayerName, SUM(goals) AS goals /*, SUM(...) AS ...,*/
    FROM Table2
    GROUP BY PlayerId, PlayerName
) AS t2 ON t2.PlayerId = t1.PlayerId
ORDER BY PlayerId /*or PlayerName, PlayerId*/
3 
Select
    PlayerID,
    PlayerName,
    Sum(Goals) As Goals
From
(
    Select PlayerID, PlayerName, Goals From Table1
    Union All Select PlayerID, PlayerName, Goals From Table2
) ALL
Group By PlayerID, PlayerName

Although a better database design would be to put all seasons in a single table with an additional column to indicate in which season the goals were scored.