# Thread: SUM on TOP 3

1. Registered User
Join Date
May 2004
Posts
19

## Unanswered: SUM on TOP 3

Hi,

SQL 2000 problem

I have a group of players with a finish position which I need to find the top player group from, this is based on the lowest total of the top 3 finishers for a group.

To qualify as a winner the player group must have 3 or more members, players can enter under a group name with two or even 1 member, but they cannot win the team event.

Table structure is simple it contains player groupname and the score/finishing position for the individual finisher

e.g.

PlayerGroup1, 10
PlayerGroup1, 15
PlayerGroup1, 20
PlayerGroup1, 35
PlayerGroup1, 57
PlayerGroup2, 3
PlayerGroup2, 16
PlayerGroup2, 17
PlayerGroup2, 45
PlayerGroup 3, 2
PlayerGroup4, 1
PlayerGroup4, 4

PlayerGroup3 are eliminated by virtue of only having 1 member, the same for PlayerGroup 4 who only have 2 members.

I now need to find the sum of the top 3 scores for PlayerGroup1 and PlayerGroup2 with the lowest sum being the winning team.

Here is my SQL so far which brings back all the teams with more than 2 members, but the sum is for all entries and not just the top 3 for each group.

SELECT PlayerGroup, SUM(RacePosition) AS Total
FROM ClubEntries
GROUP BY PlayerGroup, RacePosition HAVING COUNT(ClubName) > 2
ORDER BY Total

How do I factor into this query to only count or sum up the top 3 finishers for each club?

Any help here would be most helpful.

Thanks.
Last edited by g2bam; 06-02-09 at 12:04.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Groups with at least 3 members:
Code:
```SELECT group_name
FROM   groups
GROUP
BY group_name
HAVING Count(*) >= 3```
Join this derivation back to your members table to retrieve the scores for members in eligible groups.
Code:
```SELECT g.group_name
, s.score
FROM   scores As s
INNER
JOIN (
SELECT group_name
FROM   groups
GROUP
BY group_name
HAVING Count(*) >= 3
) As g
ON s.group_name = g.group_name```
Use the Row_Number() windowed function to identify the top 3 scores per group
Code:
```SELECT group_name
, Sum(score)
FROM   (
SELECT g.group_name
, s.score
, Row_Number() OVER (PARTITION BY g.group_name ORDER BY s.score DESC) As row_num
FROM   scores As s
INNER
JOIN (
SELECT group_name
FROM   groups
GROUP
BY group_name
HAVING Count(*) >= 3
) As g
ON s.group_name = g.group_name
) As an_alias
WHERE  row_num <= 3```
Completely untested but should work

3. Registered User
Join Date
May 2004
Posts
19
Thanks for this wonderful code, but unfortunately its not going to work on SQL 2000 with Row_Number() function.

I should also state there is an IDENTITY column for all records too, commencing 1 BY 1, perhaps the ID for these can be used as we can't use Row_Number?

Sorry for that omission in the first post I placed.

4. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
Try this:

SELECT PlayerGroup, SUM(score) TotalScore
FROM
(
SELECT t1.PlayerGroup, t1.score
FROM tbl t1
JOIN tbl t2
ON t1.PlayerGroup = t2.PlayerGroup
WHERE t1.score < t2.score
OR (t1.score = t2.score
AND t1.id <= t2.id)
GROUP BY t1.PlayerGroup,t1.score, t1.id
HAVING COUNT(*)<=3
) t
GROUP BY PlayerGroup
HAVING COUNT(*)=3
ORDER BY TotalScore;

5. Registered User
Join Date
May 2009
Posts
509
g2bam, Do you have a know, finite number of PlayerGroups? The only way I can see that this might be doable in just SQL is to hardcode the PlayerGroups into the query or do Joins/Subquerys of a set number. If you were using SQL Server 2005, there would be easier/better ways to accomplish this.

Otherwise you will need a Stored Procedure (or possibly a table function) to do the looping required. The logic in the Stored Procedure/Function is pretty easy (Get a List and Count of the Distinct PlayerGroups with 3 or more people, Loop on the Count, and get the Top 3 for Each PlayerGroup as you go through the loop).

You can try dportas's query but I just don't see it working. This is based on ID being the Identity column you mentioned. This number is 'random' in relation to the PlayerGroups so I can't see T1.ID <= T2.ID doing anything (but I could be wrong).

For example, if the PlayerGroups were entered in no particular order, the data could be:

1 PlayerGroup1 10
2 PlayerGroup3 2
3 PlayerGroup4 1
4 PlayerGroup2 16
5 PlayerGroup4 4

Now, if the data was entered in a strict Sort Order (All PlayerGroup1 in order by Score, then PlayerGroup 2 in order by Score, etc.), then this could work. You would in effect have the ROW_NUMBER function that sequential numbers the rows in a set sort order as gvee suggested.

Despite my reservations, I hope it does work for you.

6. Registered User
Join Date
May 2004
Posts
19
Hi Again,

Thanks for all the input, I am glad to see this really is quite a stinker of a problem, I was getting worried about myself!

dportas: - Thanks for your code, but like what has just been said, due to the way the ID's stack up, I get the 2nd to 4th records in some cases and not the top 3.

Now I think I may be able somehow to decant the top 3 records for each club into a temp table and simply do my select from that where all records will be clubs with 3 members, already picked by top 3 and I can select the lowest sum from that.

I was after a single SELECT for this job, always best and the most efficient way to do it, but temp tables do provide a little safety bridge at times.

I will test that and see if I can get that set up to work and let everyone know how it goes.

But if any of you guru's wish to continue helping with the code methods, please do so, after all my plan may not work.

Thanks.

7. Registered User
Join Date
Nov 2004
Location
Posts
58
Is this what you are looking for?

select
a.playergroups,
sum(b.rank)
from
players a
left join
(
select
a.Playergroups
,a.rank
from players a
inner join
players b
on
a.playergroups = b.playergroups
and
a.rank <= b.rank
group by
a.playergroups
,a.rank
having count(a.rank) <= 3
) b
on
a.playergroups = b.playergroups
and a.rank=b.rank
group by a.playergroups having count(a.rank)>2

here is my quasitable (players)
playergroups rank
pgroup1 10
pgroup1 15
pgroup1 25
pgroup1 30
pgroup1 57
pgroup2 3
pgroup2 16
pgroup2 17
pgroup2 45
pgroup3 2
pgroup4 1
pgroup4 4

and this is the final result
pgroup1 112
pgroup2 78

8. Registered User
Join Date
May 2004
Posts
19
burkular: - The result I am after would be..

pgroup2 36
pgroup1 50

With pgroup3 & 4 not included. The 'score' in this case is crossing a finish line, therefore the smaller score wins, for the winnng team this is the sum of the lowest 3 finishers, the lowest sum of the combined top 3 finishers is the winning team, but each team or group must have at least three members and only the lowest 3 scores get summed for each group.

Thanks for the code, appreciate all the other approaches different devver's take here.

This is a real poser of a query, which I never seen coming before I started coding it.

Very interesting, even for experienced coders, which I regard myself as.

9. Registered User
Join Date
Nov 2004
Location
Posts
58
The only other thing i can think of off hand is creating a cursor for each of the groups that puts the records you want into a temp table with an identity (where the identity/seq) is less than 3 then moving that to another temp table and summing for each of the distinct groupings.

ie.
pgroup1 10 1
pgroup1 15 2
pgroup1 35 3
pgroup1 40 4
pgroup1 45 5
pgroup2 3 1
pgroup2 10 2
pgroup2 15 3
pgroup2 29 4
pgroup2 35 5
pgroup3 8 1
pgroup3 10 2
pgroup3 15 3
pgroup3 20 4

Then having a query that goes like
select distinct pgroup, sum(rank)
where seq < 3
group by pgroup

DECLARE myc CURSOR

FOR select distinct a.playergroups
from players a
Group by a.playergroups

having count(a.rank) >=3

DECLARE @pgroup varchar(40)
OPEN myc

FETCH NEXT FROM myc INTO @pgroup
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--
drop table #tmp
Create table #tmp (playergroups varchar(10), rank int, sorder int IDENTITY)
Insert into #tmp
select
a.Playergroups
,a.rank
from players a
inner join
players b
on
a.playergroups = b.playergroups
where a.playergroups = @pgroup
group by
a.playergroups
,a.rank
having count(a.rank) >=3

Order by 1 asc, 2 asc

END

FETCH NEXT FROM myc INTO @pgroup

Declare @tmp2 table (playergroups varchar(10), rank int)
insert into @tmp2
select playergroups, rank from #tmp where sorder<=3

END

CLOSE myc
DEALLOCATE myc

select * from @tmp2 where playergroups is not null
Last edited by burkular; 06-03-09 at 01:40.

10. Registered User
Join Date
May 2004
Posts
19
Hi All,

dportas: - Thanks for your efforts, I juggled some stuff around and removed the ID from the table and slightly adjusted your code, and I now have what I wanted.

The code is below for your reference. Well done to you for this, a truly well thought out and knowledgeable approach.

Thanks for everyone else's contributions, its marvellous to share things likem this with dedicated professional seasoned campaigners.

SELECT TOP 1 ClubName, SUM(RacePosition) TotalScore
FROM
(
SELECT t1.ClubName, t1.RacePosition
FROM Clubs t1
INNER JOIN Clubs t2
ON t1.ClubName = t2.ClubName
WHERE t1.RacePosition >= t2.RacePosition
OR (t1.RacePosition = t2.RacePosition)
GROUP BY t1.ClubName,t1.RacePosition
HAVING COUNT(t1.ClubName)<=3
) t
WHERE UPPER(ClubName) <> 'NONE'
GROUP BY ClubName
HAVING COUNT(*)=3
ORDER BY TotalScore

This thread can now be considered closed and the issue resolved.

11. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
I don't think your query will work correctly if it's possible to have a draw for RacePosition. If that never happens then you are right that you don't need the Id as a tie-breaker.

12. Registered User
Join Date
May 2004
Posts
19
I understand that, but I have the historical records going back 8 years and because there are Males and Females and we're filtering on gender, there are huge gaps in the finish places between everyone, and each year so far the gap beteen the 1st club and the 2nd club has been in the hundreds for each set of results for each gender.

There's a 'slight' risk of a tie on the 'sum' which I can cover off if it happens year on year. Running the query against the existing years has produced exactly the club results for the last 8 years which the client obtains manually.

Happy days!

13. Registered User
Join Date
Jun 2004
Location
Long Island
Posts
696
set rowcount 3
query
set rowcount 0

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•