Basic "Group By" 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)

Basic "Group By" question

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2007-04-24 : 11:50:11

Hi, this is my table:

PlayerID, SportID, Scores
1, 1, 5
2, 1, 1
1, 5, 5
2, 5, 3
1, 1, 5
2, 1, 1
1, 2, 1
2, 2, 5

I need a query to return me the top player for each sport with the total score for each sport, eg:

PlayerID, SportID, Scores
1, 1, 10
2, 2, 5
1, 5, 5

I have tried:

Select
PlayerID, SportID, Sum(Scores) as SumScores
From
UserSportScores
Group By
PlayerID, SportID

but not right.

Is it possible to achieve this using SQL ?

Matt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 12:04:55

This is what you want ?


declare @table table
(
	PlayerID	int, 
	SportID		int, 
	Scores		int
)
insert into @table
select 1, 1, 5	union all
select 2, 1, 1	union all
select 1, 5, 5	union all
select 2, 5, 3	union all
select 1, 1, 5	union all
select 2, 1, 1	union all
select 1, 2, 1	union all
select 2, 2, 5

select	PlayerID, SportID, Scores = sum(Scores)
from	@table t
where	PlayerID in (select top 1 PlayerID 
			from 	@table x 
			where 	x.SportID = t.SportID
			group by PlayerID
			order by sum(Scores) desc)
group by PlayerID, SportID
/*
PlayerID    SportID     Scores      
----------- ----------- ----------- 
1           1           10
1           5           5
2           2           5
*/

mattyblah
Starting Member

49 Posts

Posted - 2007-04-24 : 12:07:58

do you have to do it with one query? if so, try this:
select playerid, t1.sportid, t1.score from
(select playerid, sportid, sum(scores) as score
from table
group by playerid, sportid) t1
inner join
(select sportid, max(score) as score
from
(select playerid, sportid, sum(scores) as score
from table
group by playerid, sportid) t
group by sportid) t2
on t1.sportid = t2.sportid and t1.score = t2.score
order by t1.sportid asc

I'm sure someone could provide something cleaner...

Edit: Wow, the above one is much cleaner and more efficient.

matkwan
Starting Member

36 Posts

Posted - 2007-04-24 : 12:25:47

Thanks guys. I prefer the 1 query solution by MattyBlah.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 12:47:51

Look at the execution plans. Which is more efficient? Khtan also wrote a 1-query solution

quote:

select	PlayerID, SportID, Scores = sum(Scores)
from	@table t
where	PlayerID in (select top 1 PlayerID 
			from 	@table x 
			where 	x.SportID = t.SportID
			group by PlayerID
			order by sum(Scores) desc)
group by PlayerID, SportID

Peter Larsson
Helsingborg, Sweden

mattyblah
Starting Member

49 Posts

Posted - 2007-04-24 : 17:25:31

I was gonna say. Looks like kh's should be more efficient and it's also a 1-query (relative term) solution. Just substitute your table for @table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources