How can I combine two views into one to condense my data?-CodePudding

I have two tables, one with names of players and the # of games won and one with names of players and the # of games lost. I'd like to combine them to show the names of players, # games won and # of games lost into one table

Also would like to be able to show # of games played by adding games won to games lost

table1

name	g_won
a	2
b	4

table2

name	g_lost
a	2
c	5

would like the new table to return:

name	g_won	g_lost	g_played
a	2	2	4
b	4	0	4
c	0	5	5

CodePudding user response：

You could obtain your desired result by unioning a left or right outer-join of each table and then aggregating. You could create this as a view to reference it as another table.

select name, 
    Sum(g_won) g_won, Sum(g_lost) g_lost, 
    Sum(g_won)   Sum(g_lost) g_played
from (
    select t1.name, g_won, 0 g_lost
    from t1
    left join t2 on t2.name=t1.name
    union
    select t2.name, 0 g_won, g_lost
    from t2
    left join t1 on t2.name=t1.name
)t
group by name;

See DB<>Fiddle

CodePudding user response：

You can apply a UNION operation and then select the SUM for each g_won and g_lost:

SELECT name,
       MAX(g_won)        AS g_won,
       MAX(g_lost)       AS g_lost
FROM (SELECT name,
             g_won,
             0 AS g_lost
      FROM table1
      UNION
      SELECT name,
             0 AS g_won,
             g_lost
      FROM table2            ) tab 
GROUP BY name

Try it here.