Top Value for a subgroup

R

Richardson

This should be simple, but I can't seem to make it work.
I have a summation query that is grouped by member id #s and sums their
scores. At the end of the year, we automatically drop the lowest scores and
only take the top 15 scores per member.
I know there is a top values option in the query, but isn't letting me take
the top 15 values per group, only the top 15 values for the entire query.
Can anyone tell me how to specify to group by member, and then only show and
sum the top 15 values per member for the year?

Thanks in advance,
Lori
 
B

Brian Camire

For starters, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[Member ID],
Sum([Your Table].[Score]) AS [Top 15 Total Score]
FROM
[Your Table]
WHERE
[Your Table].[Score] IN
(SELECT TOP 15
[Self].[Score]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Member ID] = [Your Table].[Member ID]
ORDER BY
[Self].[Score] DESC)
GROUP BY
[Your Table].[Member ID]

However, the SELECT TOP 15 subquery may not return exactly 15 records.

It may return more than 15 if there are "ties". For example, if a member
had 20 scores, and they were all the same, the SELECT TOP 15 subquery would
return 20 records. This would mean that for that member you would be
totalling 20 scores instead of 15. To get around this, you need a field (or
a combination of fields) that uniquely identifies each record in your table.
Assuming you have such a field and it's named "Score ID", changing the ORDER
BY clause in the above SQL to the following should solve this problem:

ORDER BY
[Self].[Score] DESC,
[Self].[Score ID]

The SELECT TOP 15 subquery may return fewer than 15 records if there were
fewer than 15 scores for a member. The query could be modified to filter
these out. Post back if you're interested.
 
G

Gerald Stanley

If the table name is MemberScores, the primary key is
memberScoresId , the member is memberId and the score is
memberScore, the SQL should be

SELECT M.memberId, Sum(M.memberScore) AS memberTotalScore
FROM MemberScores AS M
WHERE (((M.memberScoresId) In (SELECT TOP 15
M2.memberScoresId From MemberScores M2 WHERE M2.memberId =
M.memberId ORDER BY M2.memberScore DESC)))
GROUP BY M.memberId;

Hope This Helps
Gerald Stanley MCSD
 
R

Richardson

Thank you for the help. There is an ID field already, RegistrationID.
There will porbably be several ties in valuesl. Also, some individuals will
have less than 15 scores, some will have exactly 15, and others will have
more than 15.

Lori

Brian Camire said:
For starters, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[Member ID],
Sum([Your Table].[Score]) AS [Top 15 Total Score]
FROM
[Your Table]
WHERE
[Your Table].[Score] IN
(SELECT TOP 15
[Self].[Score]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Member ID] = [Your Table].[Member ID]
ORDER BY
[Self].[Score] DESC)
GROUP BY
[Your Table].[Member ID]

However, the SELECT TOP 15 subquery may not return exactly 15 records.

It may return more than 15 if there are "ties". For example, if a member
had 20 scores, and they were all the same, the SELECT TOP 15 subquery would
return 20 records. This would mean that for that member you would be
totalling 20 scores instead of 15. To get around this, you need a field (or
a combination of fields) that uniquely identifies each record in your table.
Assuming you have such a field and it's named "Score ID", changing the ORDER
BY clause in the above SQL to the following should solve this problem:

ORDER BY
[Self].[Score] DESC,
[Self].[Score ID]

The SELECT TOP 15 subquery may return fewer than 15 records if there were
fewer than 15 scores for a member. The query could be modified to filter
these out. Post back if you're interested.


Richardson said:
This should be simple, but I can't seem to make it work.
I have a summation query that is grouped by member id #s and sums their
scores. At the end of the year, we automatically drop the lowest scores and
only take the top 15 scores per member.
I know there is a top values option in the query, but isn't letting me take
the top 15 values per group, only the top 15 values for the entire query.
Can anyone tell me how to specify to group by member, and then only show and
sum the top 15 values per member for the year?

Thanks in advance,
Lori
Click to expand...
Click to expand...
 
B

Brian Camire

If you want to exclude members from the results that have less than 15
scores, add

HAVING
Count(*) < 15

to the end of the SQL statement in my previous post.

Richardson said:
Thank you for the help. There is an ID field already, RegistrationID.
There will porbably be several ties in valuesl. Also, some individuals will
have less than 15 scores, some will have exactly 15, and others will have
more than 15.

Lori

Brian Camire said:
For starters, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[Member ID],
Sum([Your Table].[Score]) AS [Top 15 Total Score]
FROM
[Your Table]
WHERE
[Your Table].[Score] IN
(SELECT TOP 15
[Self].[Score]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Member ID] = [Your Table].[Member ID]
ORDER BY
[Self].[Score] DESC)
GROUP BY
[Your Table].[Member ID]

However, the SELECT TOP 15 subquery may not return exactly 15 records.

It may return more than 15 if there are "ties". For example, if a member
had 20 scores, and they were all the same, the SELECT TOP 15 subquery would
return 20 records. This would mean that for that member you would be
totalling 20 scores instead of 15. To get around this, you need a field (or
a combination of fields) that uniquely identifies each record in your table.
Assuming you have such a field and it's named "Score ID", changing the ORDER
BY clause in the above SQL to the following should solve this problem:

ORDER BY
[Self].[Score] DESC,
[Self].[Score ID]

The SELECT TOP 15 subquery may return fewer than 15 records if there were
fewer than 15 scores for a member. The query could be modified to filter
these out. Post back if you're interested.


Richardson said:
This should be simple, but I can't seem to make it work.
I have a summation query that is grouped by member id #s and sums their
scores. At the end of the year, we automatically drop the lowest
Click to expand...
scores
and
only take the top 15 scores per member.
I know there is a top values option in the query, but isn't letting me take
the top 15 values per group, only the top 15 values for the entire query.
Can anyone tell me how to specify to group by member, and then only
Click to expand...
show
and
sum the top 15 values per member for the year?

Thanks in advance,
Lori
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Camire

That should have been:

HAVING Count(*) >= 15

Brian Camire said:
If you want to exclude members from the results that have less than 15
scores, add

HAVING
Count(*) < 15

to the end of the SQL statement in my previous post.

Richardson said:
Thank you for the help. There is an ID field already, RegistrationID.
There will porbably be several ties in valuesl. Also, some individuals will
have less than 15 scores, some will have exactly 15, and others will have
more than 15.

Lori

Brian Camire said:
For starters, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[Member ID],
Sum([Your Table].[Score]) AS [Top 15 Total Score]
FROM
[Your Table]
WHERE
[Your Table].[Score] IN
(SELECT TOP 15
[Self].[Score]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Member ID] = [Your Table].[Member ID]
ORDER BY
[Self].[Score] DESC)
GROUP BY
[Your Table].[Member ID]

However, the SELECT TOP 15 subquery may not return exactly 15 records.

It may return more than 15 if there are "ties". For example, if a member
had 20 scores, and they were all the same, the SELECT TOP 15 subquery would
return 20 records. This would mean that for that member you would be
totalling 20 scores instead of 15. To get around this, you need a
Click to expand...
field
(or
a combination of fields) that uniquely identifies each record in your table.
Assuming you have such a field and it's named "Score ID", changing the ORDER
BY clause in the above SQL to the following should solve this problem:

ORDER BY
[Self].[Score] DESC,
[Self].[Score ID]

The SELECT TOP 15 subquery may return fewer than 15 records if there were
fewer than 15 scores for a member. The query could be modified to filter
these out. Post back if you're interested.


This should be simple, but I can't seem to make it work.
I have a summation query that is grouped by member id #s and sums their
scores. At the end of the year, we automatically drop the lowest scores
and
only take the top 15 scores per member.
I know there is a top values option in the query, but isn't letting me
take
the top 15 values per group, only the top 15 values for the entire query.
Can anyone tell me how to specify to group by member, and then only show
and
sum the top 15 values per member for the year?

Thanks in advance,
Lori
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Top 15 1
Sum Top n Scores 4
SumTop 3 Values of Query Result 1
TOP values 4
hard count in top values subquery 8
Ranking the records. 2
Top 3 by Sum of Value 4
Need help with query to sum top N values in group and rank the results 4

Top