• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

follow up to a previous question - hockey stats query not returning correct results

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
		 from tPlayerStats 
		 group by scoredBy ) as ps
		  on ps.scoredBy = pgp.playerID 
		left outer join (select assist1,COUNT(*) as assists1
		 from tPlayerStats 
		 group by assist1 ) as ps2
		 on ps2.assist1 = pgp.playerID 
		left outer join ( select assist2,COUNT(*) as assists2
		 from tPlayerStats 
		 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;

Open in new window

Big Monty
Big Monty
  • 2
1 Solution
You're restricting your main query with

where pgp.gameID = 102
and pgp.teamID = 100

but you don't have these constraints on the three queries that you're using for scores and assists .....


Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
its amazing what a nights worth of sleep can do :) I noticed it this morning and fixed it. I'll give ya the points because you were spot on with the solution, thanks!
Thanks! Well done for finding it!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now