Creating league table - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Creating league table

Author

Topic

Premier2k
Starting Member

3 Posts

Posted - 2010-08-08 : 11:48:58

Hi everyone,

I'm fairly new to SQL and I'm stuck! I've been banging my head against a wall today trying to figure this out.
I have my database which tracks poker stats from our games.
I have a game table, attendance table and bonus table.

I am trying to get details from all these tables and generate a league from it.

This is what I have so far:

SELECT player.playerid, attendees.pointsearned
INTO temptable
FROM game, player, attendees
	WHERE	player.playerid = attendees.playerid 
	AND     attendees.gamedate = game.gamedate
	AND		player.playerid = attendees.playerid
	AND		attendees.leagueyear = '2010'
	AND		pointsearned > 0
	AND		game.leagueyear = attendees.leagueyear
SELECT playerid, SUM(pointsearned)
AS Points
FROM temptable
GROUP BY playerid
ORDER BY playerid ASC

SELECT bonus.playerid, bonus.gamedate
INTO tempbonus
FROM bonus, player, game
WHERE bonus.leagueyear = '2010' AND game.gamedate = bonus.gamedate AND player.playerid = bonus.playerid
ORDER BY bonus.playerid
SELECT tempbonus.playerid, COUNT(tempbonus.gamedate)
AS TimesWon
FROM tempbonus
GROUP BY playerid

This gets displays 2 temporary tables, the first displays the playerids that have points and the number of points. The second table displays the players that have bonus points along with the number of bonus points they have.
Now, I need to add together the Points column for the playerid and add it to the TimesWon column matching the playerid and populate a third table so that it shows the total points for each player.
Unfortunately some players haven't won either any points in the game or not won any bonus points so don't appear in one (or both) of the columns.

Does that make sense? I am really stuck!
Can anyone help me out? If you need any more info please let me know.

Thanks,

Premier2k

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 12:42:40

[code]
select
tt.playerID
,sum(tt.pointsearned) as nof_points
,count(tb.gamedate) as nof_games
from
temptable as tt
left outer join tempbonus as tb
on tt.playerID = tb.playerID
group by playerID
[/code]

Premier2k
Starting Member

3 Posts

Posted - 2010-08-08 : 12:58:11

ooh, that looks like it almost works!

But I'm getting an error?

Msg 209, Level 16, State 1, Line 9
Ambiguous column name 'playerID'

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 13:03:12

change last line to:

group by tt.playerID

Alias is missing!

Premier2k
Starting Member

3 Posts

Posted - 2010-08-08 : 13:13:39

Yeah I just spotted that, thanks lol

It doesn't look right still. It looks like you're counting the number of games? I don't need the number of games the player has played added on. I just need the number in the bonus table and the points scored added together
It brings back a table with values for nof_points much higher than I would expect.

If I ran my script above and checked player 2 then he has 14 points from the main games and 1 point from the bonus game bringing his total to 15. Yet if I then run yours to check this he only 14?

Thanks for your help with this!

Premier2k

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 14:09:21

please post also some data, with this particular example that you are describing. and the desired results.

i'm sure there is just a minor change needed to get your desired results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources