Sum Top n Scores

[Paul W Smith]

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID =
T.PlayerID AND T1.Score >= T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC;

The above query works to a point - it is designed to produce a metric which
is the top n scores for a player, where n is a moving number depending on
which week of the season we are at.

The above query works fine when each score a player makes is different, but
fails when a player has multiple scores covering the 'cut-off' point e.g.

Data

Player 1 scores - 23, 45, 24, 33, 12, 20 - if n = 5 the query would return
145 which is the sum of the top 5 scores.

Player 1 scores - 23, 45, 24, 33, 12, 20, 20 - if n now equals 6 the
query returns 125. This is because it does not count one of the 20s as it
should, it sums the top 5 scores even though I want the top 6.

I haver to admit I do not fully understand the query, having been given it a
while ago from this forum.

If anyone can assist me in amending the query to achieve the results I have
tried to decsibe above I would be very very grateful.

 

[Michel Walsh]

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID =
T.PlayerID AND T1.Score >=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC, T.PlayerID ;



I just added

, T.PlayerID

in the ORDER BY clause. That breaks equality ties: ie, if score are equal,
then among all these, order the rows accordingly to the PlayerID.




Hoping it may help,
Vanderghast, Access MVP

 

[PWS]

Am I doing something wrong?

I have added t.PlayerID to the ORDER clause and it makes no
difference????

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID =
T.PlayerID AND T1.Score>=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC, T.PlayerID ;

I just added

, T.PlayerID

in the ORDER BY clause. That breaks equality ties: ie, ifscoreare equal,
then among all these, order the rows accordingly to the PlayerID.

Hoping it may help,
Vanderghast, Access MVP

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID =
T.PlayerID AND T1.Score>=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName] & " (" & [tPlayers]![TeamName] & ")",
tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC;

The above query works to a point - it is designed to produce a metric
which is the topnscores for a player, wherenis a moving number
depending on which week of the season we are at.

The above query works fine when eachscorea player makes is different,
but fails when a player has multiple scores covering the 'cut-off' point
e.g.

Player 1 scores - 23, 45, 24, 33, 12, 20 - ifn= 5 the query would
return 145 which is the sum of the top 5 scores.

Player 1 scores - 23, 45, 24, 33, 12, 20, 20 - ifnnow equals 6 the
query returns 125. This is because it does not count one of the 20s as it
should, it sums the top 5 scores even though I want the top 6.

I haver to admit I do not fully understand the query, having been given it
a while ago from this forum.

If anyone can assist me in amending the query to achieve the results I
have tried to decsibe above I would be very very grateful.- Hide quoted text -

- Show quoted text -

 

[Michel Walsh]

Hi,

It *may* be possible that a given score occurs more than once, for a given
player?

If you have a primary key field, add it:


ORDER BY Sum(T.Score) DESC, T.PlayerID, T.primaryKeyFieldHere



Hoping it may help,
Vanderghast, Access MVP

Am I doing something wrong?

I have added t.PlayerID to the ORDER clause and it makes no
difference????

SELECT [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID
=
T.PlayerID AND T1.Score>=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC, T.PlayerID ;

I just added

, T.PlayerID

in the ORDER BY clause. That breaks equality ties: ie, ifscoreare equal,
then among all these, order the rows accordingly to the PlayerID.

Hoping it may help,
Vanderghast, Access MVP

message

SELECT [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS
Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID
=
T.PlayerID AND T1.Score>=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName] & " (" & [tPlayers]![TeamName] & ")",
tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC;

The above query works to a point - it is designed to produce a metric
which is the topnscores for a player, wherenis a moving number
depending on which week of the season we are at.

The above query works fine when eachscorea player makes is different,
but fails when a player has multiple scores covering the 'cut-off'
point
e.g.

Player 1 scores - 23, 45, 24, 33, 12, 20 - ifn= 5 the query would
return 145 which is the sum of the top 5 scores.

Player 1 scores - 23, 45, 24, 33, 12, 20, 20 - ifnnow equals 6 the
query returns 125. This is because it does not count one of the 20s as
it
should, it sums the top 5 scores even though I want the top 6.

I haver to admit I do not fully understand the query, having been given
it
a while ago from this forum.

If anyone can assist me in amending the query to achieve the results I
have tried to decsibe above I would be very very grateful.- Hide quoted
text -

- Show quoted text -

 

[PWS]

The problem is that as the SQL below shows T is virtual table and so
does not have a primary key.

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]!
[PlayerSecondName] & " (" & [tPlayers]![TeamName] & ")" AS FullName,
Sum(T.Score) AS Ranking, tPlayers.PlayerID FROM LeagueRoundsPlayed,
___PlayerRankings AS T INNER JOIN tPlayers ON T.PlayerID =
tPlayers.PlayerID WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings
T1 WHERE T1.PlayerID = T.PlayerID AND
T1.Score>=T.Score))<=[LeagueRoundsPlayed]![RankingGames])) GROUP BY
[tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC, T.PlayerID ;

Maybe if you can tell me how to add a primary key to this virtual T
table....

Alternately maybe you could just come up with your own query which
does what I require.

I appreciate your assistance.

PWS

Hi,

It *may* be possible that a given score occurs more than once, for a given
player?

If you have a primary key field, add it:

ORDER BY Sum(T.Score) DESC, T.PlayerID, T.primaryKeyFieldHere

Hoping it may help,
Vanderghast, Access MVP

Am I doing something wrong?

I have added t.PlayerID to the ORDER clause and it makes no
difference????

SELECT [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID
=
T.PlayerID AND T1.Score>=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC, T.PlayerID ;
I just added
, T.PlayerID
in the ORDER BY clause. That breaks equality ties: ie, ifscoreare equal,
then among all these, order the rows accordingly to the PlayerID.
Hoping it may help,
Vanderghast, Access MVP
message
SELECT [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS
Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID
=
T.PlayerID AND T1.Score>=
T.Score))<=[LeagueRoundsPlayed]![RankingGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " &
[tPlayers]![PlayerSecondName] & " (" & [tPlayers]![TeamName] & ")",
tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC;
The above query works to a point - it is designed to produce a metric
which is the topnscores for a player, wherenis a moving number
depending on which week of the season we are at.
The above query works fine when eachscorea player makes is different,
but fails when a player has multiple scores covering the 'cut-off'
point
e.g.
Data
Player 1 scores - 23, 45, 24, 33, 12, 20 - ifn= 5 the query would
return 145 which is the sum of the top 5 scores.
Player 1 scores - 23, 45, 24, 33, 12, 20, 20 - ifnnow equals 6 the
query returns 125. This is because it does not count one of the 20s as
it
should, it sums the top 5 scores even though I want the top 6.
I haver to admit I do not fully understand the query, having been given
it
a while ago from this forum.
If anyone can assist me in amending the query to achieve the results I
have tried to decsibe above I would be very very grateful.- Hide quoted
text -
- Show quoted text -- Hide quoted text -

- Show quoted text -