SQL JOIN with 2 aggregates returning incorrect results

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)

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