Top Value for a subgroup

  • Thread starter Thread starter Richardson
  • Start date Start date
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
 
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.
 
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
 
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...
 
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...
 
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...
 
Back
Top