this is a follow up to the following question:
the solution provided worked with a small amount of test data (or at least i thought it did!), but it turns out it's returning stats for the entire season, and not just a particular game. can anybody spot why?
Thanks in advance!
select goalsScored = ps.scored, numAssists = isNull( ps2.assists1, 0 ) + isNull( ps3.assists2, 0 ), playerName = p.firstName + ' ' + p.lastName
from tPlayers_x_GamesPlayed pgp
inner join tPlayers p on p.playerID = pgp.playerID
left outer join (select scoredBy,COUNT(*) as scored
group by scoredBy ) as ps
on ps.scoredBy = pgp.playerID
left outer join (select assist1,COUNT(*) as assists1
group by assist1 ) as ps2
on ps2.assist1 = pgp.playerID
left outer join ( select assist2,COUNT(*) as assists2
group by assist2 ) as ps3
on ps3.assist2 = pgp.playerID
where pgp.gameID = 102
and pgp.teamID = 100
and ( isNull( p.position1, '' ) <> 'Goalie'
and isNull( p.position2, '' ) <> 'Goalie'
and isNull( p.position3, '' ) <> 'Goalie'
group by p.firstName, p.lastName, ps.scored, ps2.assists1, ps3.assists2
order by p.lastname,p.firstname;