Query for two tables with similar information

I am working on some baseball stats to improve my database abilities I have data for hitters from 2017 and 2018 MLB seasons. what I hope to accomplish is to average the number of games played, at bats and hit over the two seasons. Eventually I want to do it over five seasons but I figure I need to start somewhere. The problem is that both tables have exactly the same names for the columns. I think I need to do a UNION but I am not sure.

I am using SSMS, the Express version. I have tried a simple query, thinking it would do some thing but obviously, I should have known better. I tried:

SELECT PLAYER, g, ab, hit
FROM mlb_2017, mlb_2018
WHERE mlb_2017.PLAYER = mlb_2018.PLAYER
ORDER BY PLAYER;

​x
 
SELECT PLAYER, g, ab, hitFROM mlb_2017, mlb_2018WHERE mlb_2017.PLAYER = mlb_2018.PLAYERORDER BY PLAYER;​

this is the Excel file example. Both 2017 and 2018 have these fields exactly as shown, well, with about ten more fields that I am not using yet.

Player  Team    pos g   ab  hit 2b
abreu,jose  whit    1B  128 499 132 36
acuna,ron   brav    OF  111 433 127 26
adames,will rays    SS  85  288 80  7
adams,lane  brav    OF  26  25  6   1

 
Player  Team    pos g   ab  hit 2babreu,jose  whit    1B  128 499 132 36acuna,ron   brav    OF  111 433 127 26adames,will rays    SS  85  288 80  7adams,lane  brav    OF  26  25  6   1​

So what I want, is to combine both of the seasons into one two year total and then average them.

Answer

Try a union query:

SELECT PLAYER, AVG(hit) AS avg_hits
FROM
(
    SELECT PLAYER, hit
    FROM mlb_2017
    UNION ALL
    SELECT PLAYER, hit
    FROM mlb_2018
) t
GROUP BY PLAYER;

 
SELECT PLAYER, AVG(hit) AS avg_hitsFROM(    SELECT PLAYER, hit    FROM mlb_2017    UNION ALL    SELECT PLAYER, hit    FROM mlb_2018) tGROUP BY PLAYER;​

The idea behind the union query is that it brings the hit data for each player, for both 2017 and 2018, into a single intermediate table, which we can then aggregate to find the averages.

Advertisement

Answer