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

Posted on 2012-09-08
Last Modified: 2012-09-09
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

Question by:Big Monty
    LVL 16

    Accepted 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 .....


    LVL 32

    Author Closing Comment

    by:Big Monty
    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!
    LVL 16

    Expert Comment

    Thanks! Well done for finding it!

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now