I am trying to join 3 different tables to get how many Home Runs a player has in his career along with how many Awards they have recieved. However, I’m getting incorrect results:
Peoples PlayerId
Battings PlayerId, HomeRuns
AwardsPlayers PlayerId, AwardName
Current Attempt
SELECT TOP 25 Peoples.PlayerId, SUM(Battings.HomeRuns) as HomeRuns, COUNT(AwardsPlayers.PlayerId)
FROM Peoples
JOIN Battings ON Battings.PlayerId = Peoples.PlayerId
JOIN AwardsPlayers ON AwardsPlayers.PlayerId = Battings.PlayerId
GROUP BY Peoples.PlayerId
ORDER BY SUM(HomeRuns) desc
Result
PlayerID HomeRuns AwardCount bondsba01 35814 1034 ruthba01 23562 726 rodrial01 21576 682 mayswi01 21120 736 willite01 20319 741 griffke02 18270 667 schmimi01 18084 594 musiast01 16150 748 pujolal01 14559 414 dimagjo01 12996 468 ripkeca01 12499 609 gehrilo01 12325 425 aaronha01 12080 368 foxxji01 11748 462 ramirma02 10545 399 benchjo01 10114 442 sosasa01 9744 304 ortizda01 9738 360 piazzmi01 9394 396 winfida01 9300 460 rodriiv01 9019 667 robinfr02 8790 330 dawsoan01 8760 420 robinbr01 8576 736 hornsro01 8127 648
I am pretty confident it’s my second join Do I need to do some sort of subquery or should this work? Barry Bonds definitely does not have 35,814 Home Runs nor does he have 1,034 Awards
If I just do a single join, I get the correct output:
SELECT TOP 25 Peoples.PlayerId, SUM(Battings.HomeRuns) as HomeRuns
FROM Peoples
JOIN Battings ON Battings.PlayerId = Peoples.PlayerId
GROUP BY Peoples.PlayerId
ORDER BY SUM(HomeRuns) desc
bondsba01 762
aaronha01 755
ruthba01 714
rodrial01 696
mayswi01 660
pujolal01 633
griffke02 630
thomeji01 612
sosasa01 609
robinfr02 586
mcgwima01 583
killeha01 573
palmera01 569
jacksre01 563
ramirma02 555
schmimi01 548
ortizda01 541
mantlmi01 536
foxxji01 534
mccovwi01 521
thomafr04 521
willite01 521
bankser01 512
matheed01 512
ottme01 511
What am I doing wrong? I’m sure it’s how I’m joining my second table (AwardsPlayers)
Advertisement
Answer
I think you have two independent dimensions. The best approach is to aggregate before joining:
SELECT TOP 25 p.PlayerId, b.HomeRuns, ap.cnt
FROM Peoples p LEFT JOIN
(SELECT b.PlayerId, SUM(b.HomeRuns) as HomeRuns
FROM Battings b
GROUP BY b.PlayerId
) b
ON b.PlayerId = p.PlayerId LEFT JOIN
(SELECT ap.PlayerId, COUNT(*) as cnt
FROM AwardsPlayers ap
GROUP BY ap.PlayerId
) ap
ON ap.PlayerId = p.PlayerId
ORDER BY b.HomeRuns desc;
Result
bondsba01 762 47 aaronha01 755 16 ruthba01 714 33 rodrial01 696 31 mayswi01 660 32 pujolal01 633 23 griffke02 630 29 thomeji01 612 6 sosasa01 609 16 robinfr02 586 15 mcgwima01 583 9 killeha01 573 8 palmera01 569 8 jacksre01 563 13 ramirma02 555 19 schmimi01 548 33 ortizda01 541 18 mantlmi01 536 15 foxxji01 534 22 mccovwi01 521 10 thomafr04 521 10 willite01 521 39 bankser01 512 10 matheed01 512 4 ottme01 511 11