Multi tool use

Joining tables and returning the correct aggregation

I'm using Sean Lahman's Baseball database to aggregate runs, hits and 'at bats', wins & losses by team between the year 2010 and 2015. I want to join Teams and Batting table and use the group by function on teamID to return total runs, hits, at bats as well as wins and losses by team from the Teams table.

For instance, from the teams table I want to return wins and losses year wise

team ID Name Wins Losses Year
ARI Arizona Diamondbacks 65 97 2010
ARI Arizona Diamondbacks 94 68 2011


And from the Batting Table this is the output I want

year teamID Runs Hits At Bats
2012 ARI 734 1416 5462
2015 ARI 720 1494 5649


I tried the following query but it is returning inflated values for wins and losses columns:

select b.yearID, b.teamID, SUM(b.R) as Runs, SUM(b.H) as Hits, SUM(b.AB) as At_Bats,
t.name as Team_Name, SUM(t.W) as Wins, SUM(t.L) as Losses
from Batting b, Teams t
where b.teamID = t.teamID and b.yearID=t.yearID and b.yearID between '2010' and '2015'
group by b.teamID, b.yearID, t.name, t.W, t.L
order by b.teamID


Documentation can be found here for the database http://www.seanlahman.com/files/database/readme2017.txt

1 Answer
1

Although I can't find that in the documentation, I guess that each combination of teamID and yearID uniquely identifies a record in the Teams table. When summing up wins and losses, you multiply them by the number of related players. So just don't build sums on t.W and t.L:

select b.yearID, b.teamID, SUM(b.R) as Runs, SUM(b.H) as Hits, SUM(b.AB) as At_Bats,
t.name as Team_Name, t.W as Wins, t.L as Losses
from Batting b, Teams t
where b.teamID = t.teamID and b.yearID=t.yearID and b.yearID between '2010' and '2015'
group by b.teamID, b.yearID, t.name, t.W, t.L
order by b.teamID


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.