Get a total of the TOP 6 results

how to

[Red_Eye]

I have a data table that stores the scores for a given attendee at a given event.
The query that I run to get a leader board is as follows

SELECT SUM(Score) AS ScoreTotal ,AttendeeID,EventID FROM <TableName> GROUP BY AttendeeID,EventID ORDER BY ScoreTotal

What I now need is to produce a leader board based on the top 6 scores each attendee has. Rather than all of the scores they have.

Any suggestions.

[txbakers]

Could you get the ID numbers of each attendeed, then loop through the query you have for each attendee?

I think you could also add "limit 6" to the end of the query if you are using mySQL

[Red_Eye]

I am using SQL Server. I dont really want to loop through each attendee unless I have to

The source data looks like this

EventID; AttendeeID; Score
1; John; 10
1; Jim; 9
1; Jo; 25
2; John; 20
2; Jo; 10
2; Jim; 9

so when I run the query

I get
ScoreTotal; AttendeeID;
35; JO
30; John
18; Jim

This is fine for now but once they get more than six results I only want there top 6 Results to be part of the SUM.

[syber]

SELECT TOP 6 SUM(Score) AS ScoreTotal ,AttendeeID,EventID FROM <TableName> GROUP BY AttendeeID,EventID ORDER BY ScoreTotal DESC

[Red_Eye]

Syber, thanks for that but won't that return the TOP 6 Score totals not the top 6 scores that are then summed.

I need to get the top 6 scores for each person then add them up.

[Graham]

Red_Eye

I have the following that may work, however this assumes a couple of things:

1. That you are storing AttendeeID as a Foreign Key, and storing AttendeeDetails in a different table.

2. That there will not be duplicate Scores in the table (which will probably invalidate this solution!)

It also uses two queries.

Q1.:
SELECT tblAttendees.AttendeeName, tblAttendeeScores.Score
FROM tblAttendees INNER JOIN tblAttendeeScores ON tblAttendees.AttendeeID = tblAttendeeScores.AttendeeID
WHERE (((tblAttendeeScores.Score) In (Select Top 6 [Score] From tblAttendeeScores Where [AttendeeID]=[tblAttendees].[AttendeeID] Order By [Score] Desc)))
ORDER BY tblAttendees.AttendeeName, tblAttendeeScores.Score DESC;

We then use this query (named Query 1 Below), to produce the second query, grouping the Attendee and Summing their Top 6 scores.

Q2.:
SELECT Query1.AttendeeName, Sum(Query1.Score) AS SumOfScore
FROM Query1
GROUP BY Query1.AttendeeName;

As stated, this only works correctly if the 6 scores are ALL different for each Attendee.

To get around this, you would probably need to do some kind of ranking per Attendee.

[syber]

OK, I think I know what you want now. You need the detail to consist of the top six scores for each attendee, which will then be summed to determine the leaderboard. This solutions assumes you have a Primary Key for <TableName> and that the lowest score is the best score (like golf).

SELECT SUM(Score) AS ScoreTotal, AttendeeID, EventID  
FROM <TableName> tab1 
WHERE <primarykey> IN (SELECT TOP 6 <primarykey> 
 FROM <TableName> tab2 
 WHERE tab1.AttendeeID = tab2.AttendeeID  
 AND tab1.EventID = tab2.EventID 
 ORDER BY Score) 
GROUP BY AttendeeID,EventID  
ORDER BY ScoreTotal 

[Red_Eye]

Thanks for all your help. This is the final query that worked

SELECT SUM(Score) AS ScoreTotal, UserName FROM Attendees t1
WHERE EventID IN (SELECT TOP 6 EventID FROM Attendees t2
WHERE t1.UserName=t2.UserName ORDER BY Score DESC)
GROUP BY UserName
ORDER BY ScoreTotal DESC