Tricky SQL question - 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 2000 Forums

Transact-SQL (2000)

Tricky SQL question

Author

Topic

polkadragon
Starting Member

6 Posts

Posted - 2005-01-26 : 05:40:25

Hey all,

I'm modeling a query for a bloodbowl game site I've developed and am being stumped for the moment.

I have a table called "Match_History_Stats", which has the following fields (simplified for brevity):

CREATE TABLE [dbo].[MATCH_HISTORY_STATS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MATCH_HISTORY_ID] [int] NOT NULL ,
[PLAYERID] [int] NOT NULL ,
[ACTIONTYPEID] [int] NOT NULL ,
) ON [PRIMARY]

PlayerId is a FK to the Player table.
ActionTypeId is a link to a table with actions a player can perform (such as score a touchdown, make a pass etc). Each of these actions is worth a number of 'star player points' (eg actiontype 3 (touchdown) = 3, actiontype 2 (pass)=1), this information is not stored in the table though, it must be hardcoded in the query.

So this table will contain x records for each Match, where x is the number of player actions that have occurred in that match.

Now for the query itself.

I want to retrieve a Top 10 list of all players, based on the TOTAL Star Player Points of all their accumulated actions.

So in other words, if we have a table filled with

1, Match1, Player1, 3
2, Match1, Player1, 2
3, Match1, Player2, 3
4, Match2, Player1, 3
5, Match2, Player2, 2
6, Match3, Player3, 2

The query should return

1, Player 1, 7 Spp ( 3 + 3 + 1)
2, Player 2, 4 Spp ( 3 + 1 )
3, Player 3, 1 Spp ( 1 )

Any ideas on how to do this? I just keep staring at this.

Using SQL Server 2000.

thanks!!

Sam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 05:56:51

will this do?

select [PLAYERID] sum([ACTIONTYPEID]) as [ACTIONTYPEID]
from [MATCH_HISTORY_STATS]
group by [PLAYERID]

Go with the flow & have fun! Else fight the flow

polkadragon
Starting Member

6 Posts

Posted - 2005-01-26 : 06:00:31

Thanks for the reply, but a sum of the ID's of Actiontype will not do it, since they all have a Star Player Point value linked with them, and its the sum of the Star Player Points I need, not the sum of the Ids itself.

thanks

Sam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 06:07:39

well can you provide the table design for both tables then? and some sample data... i'm guessing it's just a simple join ...

Go with the flow & have fun! Else fight the flow

polkadragon
Starting Member

6 Posts

Posted - 2005-01-26 : 06:34:39

Here you go, creation script and sample data.
Just ignore all FK, they're not really important

CREATE TABLE [dbo].[MATCH_HISTORY_STATS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MATCH_HISTORY_ID] [int] NOT NULL ,
[PLAYERID] [int] NOT NULL ,
[PLAYERNAME] [nvarchar] (100) ,
[ACTIONTYPEID] [int] NOT NULL ,
[TEAM_ID] [int] NOT NULL ,
[TEAMNAME] [nvarchar] (100)
) ON [PRIMARY]

SET IDENTITY_INSERT match_history_stats ON

INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(2,2516,292,'Deirdre Dorunda',3,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(5,2516,291,'Aideen Aedare',4,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(17,2516,296,'Aria',4,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(20,2516,1074,'Thorgal Buttkicksson II',5,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(26,2516,754,'Ulhurkurturbar Wulharkbargnuklog',1,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(27,2516,298,'Issa',1,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(30,2516,761,'Guntson Passgir',1,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(31,2516,775,'BjÃ¶rn Boarfucksgir II',7,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(32,2516,299,'Cyane',7,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(33,2516,292,'Deirdre Dorunda',3,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(34,2516,988,'Frederika Booty',3,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(35,2516,290,'Bridget Strongbow',1,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(36,2516,776,'Karlson Dogfuckson',7,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(44,2516,988,'Frederika Booty',4,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(45,2516,988,'Frederika Booty',5,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(46,2516,988,'Frederika Booty',6,29,'Vyndheim Valkyries')

SET IDENTITY_INSERT match_history_stats OFF

thanks

S

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 08:06:07

i don't get how you hardcode the 'star player points' values nad why. can you show that?
i'm not sure this is the best way to do this....

Go with the flow & have fun! Else fight the flow

polkadragon
Starting Member

6 Posts

Posted - 2005-01-26 : 08:13:39

Actually it doesn't matter, whether it is hardcoded or not, if it makes things easier to visualise, just add a table containing the different ActionTypes and a field 'StarPlayerPoints' associated to each actiontype.

Thanks for the effort!
S

polkadragon
Starting Member

6 Posts

Posted - 2005-01-26 : 08:18:12

Just for clarity, different actiontypes are worth different amount of points.

The table Match_History_Stats is just a flat history of which player did which action. The interpretation of multiplying the actiontype with its associated Star Player Points is something that happens on a higher layer, such as business logic or (in this case) the T-SQL query.

FYI, This is the list of Actiontype vs Star Player Points:

Actiontype Star Player Points
1 1
2 2
3 3
4 2
5 2
6 2
7 5
8 2

thx

Sam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 08:20:58

aha ok. will this do?


CREATE TABLE [dbo].[MATCH_HISTORY_STATS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MATCH_HISTORY_ID] [int] NOT NULL ,
[PLAYERID] [int] NOT NULL ,
[PLAYERNAME] [nvarchar] (100) ,
[ACTIONTYPEID] [int] NOT NULL ,
[TEAM_ID] [int] NOT NULL ,
[TEAMNAME] [nvarchar] (100) 
) ON [PRIMARY]

SET IDENTITY_INSERT match_history_stats ON

INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(2,2516,292,'Deirdre Dorunda',3,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(5,2516,291,'Aideen Aedare',4,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(17,2516,296,'Aria',4,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(20,2516,1074,'Thorgal Buttkicksson II',5,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(26,2516,754,'Ulhurkurturbar Wulharkbargnuklog',1,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(27,2516,298,'Issa',1,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(30,2516,761,'Guntson Passgir',1,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(31,2516,775,'BjÃ¶rn Boarfucksgir II',7,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(32,2516,299,'Cyane',7,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(33,2516,292,'Deirdre Dorunda',3,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(34,2516,988,'Frederika Booty',3,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(35,2516,290,'Bridget Strongbow',1,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(36,2516,776,'Karlson Dogfuckson',7,63,'The Brethren of Thor')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(44,2516,988,'Frederika Booty',4,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(45,2516,988,'Frederika Booty',5,29,'Vyndheim Valkyries')
INSERT INTO [match_history_stats] ([ID],[MATCH_HISTORY_ID],[PLAYERID],[PLAYERNAME],[ACTIONTYPEID],[TEAM_ID],[TEAMNAME])VALUES(46,2516,988,'Frederika Booty',6,29,'Vyndheim Valkyries')

SET IDENTITY_INSERT match_history_stats OFF

create table points (actionTypeID int, StarPlayerPoints int) 
insert into points
select 1, 2 union all
select 2, 3 union all
select 3, 4 union all
select 4, 10 union all
select 5, 12 union all
select 6, 1 union all
select 7, 6

select * from match_history_stats
select * from points

select m.PLAYERID, sum(p.StarPlayerPoints)
from 	match_history_stats M
	inner join points P on M.actionTypeID = P.actionTypeID
group by m.PLAYERID

drop table match_history_stats
drop table points