Double Trouble Ag Function and Null Vals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Double Trouble Ag Function and Null Vals

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-28 : 14:45:35

I am attempting to produce a graph which compares baseball players for various categories, e.g Home Runs, by age. It needs to be in a cross-tab format
The following code covers most eventualities


SELECT     MAX(CASE WHEN MasterPlayers.birthMonth < 7 THEN  (Batting.yearID - MasterPlayers.birthYear) ELSE (Batting.yearID - MasterPlayers.birthYear-1) END) AS Age,   

		 MAX(CASE WHEN  (Batting.playerID='dimagdo01') THEN Batting.HR  END) AS playerA,
		MAX(CASE WHEN  (Batting.playerID='dimagvi01') THEN Batting.HR  END) AS playerB,
		MAX(CASE WHEN  (Batting.playerID='dimagdo01') THEN Batting.yearID END) AS yearA,
		MAX(CASE WHEN  (Batting.playerID='dimagvi01') THEN Batting.yearID END) AS yearB
		
		 
			
FROM         
                      Batting INNER JOIN
                      MasterPlayers ON Batting.playerID = MasterPlayers.playerID 
WHERE (MasterPlayers.playerID = 'dimagdo01'  OR MasterPlayers.playerID = 'dimagvi01') AND  (Batting.yearID - MasterPlayers.birthYear) between 15 AND 40
GROUP BY (Batting.yearID-MasterPlayers.birthYear)
ORDER BY (Batting.yearID - MasterPlayers.birthYear)

However, there are two problems
a) If a player plays for more than one club in a season I should be using


 MAX(CASE WHEN  (Batting.playerID='dimagdo01') THEN Sum(Batting.HR)  END) AS playerA,

but this results in 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

b) If there are any years in which he is absent, war, injury, premature retirement then I get Null values - which I wish amended to zero - and may omit the year altogether on the series

Are these problems surmountable?

Andrew Clark
www.majorleaguecharts.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-28 : 14:50:49

Well it seems as you need to use a subquery... Maybe something like:


SELECT     
	MAX(CASE WHEN MasterPlayers.birthMonth < 7 THEN  (Batting.yearID - MasterPlayers.birthYear) ELSE (Batting.yearID - MasterPlayers.birthYear-1) END) AS Age,   
	MAX(CASE WHEN  (Batting.playerID='dimagdo01') THEN Batting.HR  END) AS playerA,
	MAX(CASE WHEN  (Batting.playerID='dimagvi01') THEN Batting.HR  END) AS playerB,
	MAX(CASE WHEN  (Batting.playerID='dimagdo01') THEN Batting.yearID END) AS yearA,
	MAX(CASE WHEN  (Batting.playerID='dimagvi01') THEN Batting.yearID END) AS yearB
From 
	(
	Select playerId, yearId, HR = Sum(HR) From batting Group By playerId, yearId
	) as A

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-28 : 14:51:45

Declare @HR int
Set @HR = null

Select @HR, isnull(@HR,0)

Corey

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-28 : 15:48:29

Thanks for the reply but I need my hand holding even more. what would be the full code

I am looking for an output like

Age playerA yearA playerB yearB
23 15 1951 6 1988
24 0 1952 16 1989
25 8 19533 23 1990

(sorry about the appearance)

Where playerA missed the entire 1952 so there is no Batting.yearID = 1952 for him and playerB played for two teams in 1990, scoring 12 Home Runs with one and 11 for the other

Cheers

Andrew Clark
www.majorleaguecharts.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-28 : 15:57:23

try this:


SELECT     
	MAX(CASE WHEN MasterPlayers.birthMonth < 7 THEN  (Batting.yearID - MasterPlayers.birthYear) ELSE (Batting.yearID - MasterPlayers.birthYear-1) END) AS Age,   
	MAX(CASE WHEN Batting.playerID='dimagdo01' THEN Batting.HR END) AS playerA,
	MAX(CASE WHEN Batting.playerID='dimagvi01' THEN Batting.HR END) AS playerB,
	MAX(CASE WHEN Batting.playerID='dimagdo01' THEN Batting.yearID END) AS yearA,
	MAX(CASE WHEN Batting.playerID='dimagvi01' THEN Batting.yearID END) AS yearB
FROM
	(Select playerId, yearId, HR = sum(HR) From Batting Group By PlayerId, YearId) as BattingData 
INNER JOIN
	MasterPlayers 
ON BattingData.playerID = MasterPlayers.playerID 
WHERE MasterPlayers.playerID in ('dimagdo01','dimagvi01') AND  (Batting.yearID - MasterPlayers.birthYear) between 15 AND 40
GROUP BY (Batting.yearID-MasterPlayers.birthYear)
ORDER BY (Batting.yearID - MasterPlayers.birthYear)

if that doesn't work, post some sample data to work with, and preferably some table layouts...

Corey

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-28 : 16:53:35

Your code as is produces a load of messages
"The column prefix 'Batting' does not match with a table name or alias name used in the query."
chaging the FROM statement to



FROM
	(Select playerId, yearId, HR = sum(HR) From Batting Group By PlayerId, YearId) as BattingData 
INNER JOIN
	MasterPlayers 
ON BattingData.playerID = MasterPlayers.playerID INNER JOIN
	Batting 
ON BattingData.playerID = Batting.playerID

gets me back to where I started

Real data, with a different player, to show team problem

Dom Di Maggio (playerA)
MasterPlayer.playerID = dimagdo01
MasterPlayer.birthYear = 1917
MasterPlayer.birthMonth = 2

batting.playerID = dimagdo01
yearID HR teamID
1941 8 BOS
1942 14 BOS
1946 7 BOS
1947 8 BOS
1949 8 BOS
etc

Sammy Sosa (PlayerB)
MasterPlayer.playerID = sosasa01
MasterPlayer.birthYear = 1968
MasterPlayer.birthMonth = 11

batting.playerID = dimagdo01
yearID HR teamID
1989 1 TEX
1989 3 CHA
1990 15 CHA
1991 10 CHA
1992 8 CHN
1993 33 CHN
1994 25 CHN
1995 36 CHN
1996 40 CHN
1997 36 CHN
etc

Output from query

Age playerA yearA playerB yearB
20 NULL NULL 3 1989 PlayerB should be 4
21 NULL NULL 15 1990
23 8 1940 10 1991 This is SOSA's 22 nd year data
24 8 1941 8 1992
25 14 1942 33 1993
25 NULL NULL 25 1994 Repeating Age 25
26 NULL NULL 36 1995
27 NULL NULL 40 1996
29 7 1946 36 1997 Missing Age 28
30 8 1947 66 1998
etc

So you see the problems (if you can follow the tables is there a way to tab in the messages?)

Andrew

Andrew Clark
www.majorleaguecharts.com

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 19:04:23

quote:
Originally posted by AndyC

(... is there a way to tab in the messages?)

I found to put what i want to looked tabbed to put the

around it

Sorry I can't help with the query itself tho. Good luck.

- RoLY roLLs

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-28 : 19:20:57

I'm none the wiser

Andrew Clark
www.majorleaguecharts.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 08:34:39

How bout this:


Create Table #MasterPlayer (playerId varchar(20), playerName varchar(30), birthYear int, birthMonth int)
Insert Into #MasterPlayer Values ('dimagdo01', 'Dom Di Maggio', 1917, 2)

Create Table #Batting (playerId varchar(20), yearId int, HR int, teamId varchar(5))
Insert Into #Batting Values ('dimagdo01',1941,8,'BOS')
Insert Into #Batting Values ('dimagdo01',1942,14,'BOS')
Insert Into #Batting Values ('dimagdo01',1946,7,'BOS')
Insert Into #Batting Values ('dimagdo01',1947,8,'BOS')
Insert Into #Batting Values ('dimagdo01',1949,8,'BOS')
Insert Into #Batting Values ('dimagdo01',1941,8,'BOS')


Insert Into #MasterPlayer Values ('sosasa01', 'Sammy Sosa', 1968, 11)

Insert Into #Batting Values ('sosasa01',1989,1,'TEX')
Insert Into #Batting Values ('sosasa01',1989,3,'CHA')
Insert Into #Batting Values ('sosasa01',1990,15,'CHA')
Insert Into #Batting Values ('sosasa01',1991,10,'CHA')
Insert Into #Batting Values ('sosasa01',1992,8,'CHN')
Insert Into #Batting Values ('sosasa01',1993,33,'CHN')
Insert Into #Batting Values ('sosasa01',1994,25,'CHN')
Insert Into #Batting Values ('sosasa01',1995,36,'CHN')
Insert Into #Batting Values ('sosasa01',1996,40,'CHN')
Insert Into #Batting Values ('sosasa01',1997,36,'CHN')

Declare @playerA varchar(20),
	@playerB varchar(20)
Set @playerA = 'dimagdo01'
Set @playerB = 'sosasa01'

Select
	Age,
	PlayerA = sum(case when playerId = @playerA then HR else null end),
	YearA = max(case when playerId = @playerA then YearId else null end),
	PlayerB = sum(case when playerId = @playerB then HR else null end),
	YearB = max(case when playerId = @playerB then YearId else null end)
From
	(
	Select 
		B.PlayerId, 
		Age = Case 
			When MP.birthMonth < 7 then (B.yearID - MP.birthYear) 
			Else (B.yearID - MP.birthYear-1) End,
		B.YearId,
		B.HR
	From #Batting as B
	Left Join #MasterPlayer as MP
	On B.playerId = MP.playerId
	Where B.playerId in (@playerA,@playerB)
	) as byAge
Group By Age

Drop Table #MasterPlayer
Drop Table #Batting

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-29 : 08:59:41

Much like the table of years, you need a table of "ages", from, say, 18 to 50 or whatever you need.

that list of ages becomes your primary table in the query -- this way, no gaps will ever occur in your data.

From there, then, you can get your results like this:

select Ages.Age, PlayerA.*, PlayerB.*
from Ages
left outer join
<select stats from Player A, grouped by his Age> PLayer A
on
Ages.Age = PlayerA.Age
left outer join
<select stats from Player B, grouped by his Age> PLayer B
on
Ages.Age = PlayerB.Age
WHERE
(PlayerA.Age is not null) or (PlayerB.Age is not null)

This often confuses people, where the primary source of a query needs to be something like a table of numbers or a table of years or dates. They often try to derive the structure of the query from the transactions, but then gaps in the data cause gaps in the results.

- Jeff

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-29 : 14:40:33

Corey,
Thanks That's great. I was able to adapt it fine to real data and parameterize the category. e.g HR
I still need to play around with Null and zeros. Zeros are better graph wise when the player is out mid-career, nulls if he does not start as young (or finish as old )as the other guy. But this is a relatively minor point
Hopefully, you enjoy baseball stats and can enjoy the fruits of your labor. The revised version should be on the site by tomorrow at the latest

Andrew Clark
www.majorleaguecharts.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 14:59:31

Sites pretty cool... I know almost nothing about Flash or Pro Sports... so maybe I can learn a little there

Glad I could help!!

Corey

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 15:47:36

Speaking of the site, yes, very cool! I know flash but never got to the db level to grab info from the db. I always thought of making a site like this....but guess you beat me to it

- RoLY roLLs

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-29 : 16:38:09

Thanks guys. With the help of you SQL experts I'm hoping it will
be a bit special. Give me some suggestions on what you would like to see there

Andrew Clark
www.majorleaguecharts.com

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 18:55:14

andy, how about incorporating something like a dropdown with names in them already? maybe some not-so-hard-core fans may not know who to look for or spell the name right.

- RoLY roLLs

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-07-29 : 20:36:19

Worth thinking about - though it might delay even longer seeing
some data. Perhaps I could set up a drop down list but select one to kick off. Then when the first graph comes up the user could either select from that list or enter his own player(s)
How many might be on the list and who would you suggest? I would have a seperate list for pichers - which I have yet to do
The latest version is now up with the corrections made and a few other improvements made

Andrew Clark
www.majorleaguecharts.com

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 20:56:01

one thought i had in mind to make the list short was to make a frequently select player list, of about 10/20/30 players?

- RoLY roLLs

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-01 : 07:14:16

Andy: are the stats on you site up to date? I work for a betting company and I know of some people that surely would find your site interesting that bet heavily on MLB, and stats are a bettors friend :)

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-08-01 : 07:25:03

The soccer data is updated regularly during the season
At the moment, the MLB info is historical but that
could be amended. If I know what stats people bet on then
we might be able to collect relevant and graph them attractively to
encourage the bettors. Please have your guys contact me off-line
Cheers

Andrew Clark
www.majorleaguecharts.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources