Query Help Needed

[EA]

My problem is this. I have a query which produces all the performances,
indexed by PlayerID e.g.

PlayerID Score
1 12
1 12
1 6
2 6
2 8
2 6
3 8
3 8
4 4
4 6

What I would like is a query that returns the average of the top n number of
scores for each player in a query e.g. n = 2 for this example

PlayerID Score
1 12
2 7
3 8
4 5

Can anyone help?

 

[Allen Browne]

You will need a primary key field in the table to achive this.

The example below assumes the primary key is named ID, and the table is
Table1:

SELECT ID, Table1.PlayerID, Avg(Table1.Score) AS AvgOfScore
FROM Table1
WHERE ID IN (SELECT TOP 2 ID FROM Table1 AS Dupe
WHERE Dupe.PlayerID = Table1.PlayerID
ORDER BY Dupe.Score DESC, Dupe.ID)
GROUP BY Table1.PlayerID;

The subquery selects the top 2 records for the player in the main query.
The main query is limited to those 2 records, so the average reflects those
2 only.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

(BTW, the 2 cannot be a parameter: you will need to change the number in the
query statement.)

 

[EA]

Thanks Allen for your reply - I am grateful for the reference to subqueries
which are definitely not understood by me. I will try to understand the
reference.

Am I right in thinking that your code below requires me to manually change
the text of the SQL everytime I want to change the n number of items to be
averaged?

I would prefer to do this via some argument so I do not have to monitor my
code as the number of scores each player gets increases - Ideally I would
like it to be a function of the number of games played i.e. 80%. I do have
a query that returns the number of games played (Query3).

I assume the amendment below would not work:

SELECT ID, Table1.PlayerID, Avg(Table1.Score) AS AvgOfScore
FROM Table1
WHERE ID IN (SELECT TOP -- INT(Query3.NoOfGames * 0.8) -- ID FROM Table1
AS Dupe
WHERE Dupe.PlayerID = Table1.PlayerID
ORDER BY Dupe.Score DESC, Dupe.ID)
GROUP BY Table1.PlayerID;

Can you think of a way of doing what I am trying to achieve or is it
impossible?

 

[Allen Browne]

Karl Dewey posted an alternative.
Sometimes posts dont' show up, so just in case you didn't see his post:

SELECT T.PlayerID, Avg(T.Score) AS AvgOfScore
FROM EA_Table AS T
WHERE ((((SELECT COUNT(*)
FROM [EA_Table] T1
WHERE T1.PlayerID = T.PlayerID
AND T1.Score >= T.Score))<=[Enter number of points]))
GROUP BY T.PlayerID
ORDER BY T.PlayerID, Avg(T.Score) DESC;

 

[raskew via AccessMonster.com]

Allen -

Not sure why but couldn't get your solution to work as published. The
following modification however does:

SELECT
Table1.PlayerID
, Avg(Table1.Score) AS AvgOfScore
FROM
Table1
WHERE
(((Table1.ID) In (
SELECT
Top 2 ID
FROM
Table1 AS Dupe
WHERE
Dupe.PlayerID = Table1.PlayerID
ORDER BY
Dupe.Score DESC
, Dupe.ID)))
GROUP BY
Table1.PlayerID;

Bob

Karl Dewey posted an alternative.
Sometimes posts dont' show up, so just in case you didn't see his post:

SELECT T.PlayerID, Avg(T.Score) AS AvgOfScore
FROM EA_Table AS T
WHERE ((((SELECT COUNT(*)
FROM [EA_Table] T1
WHERE T1.PlayerID = T.PlayerID
AND T1.Score >= T.Score))<=[Enter number of points]))
GROUP BY T.PlayerID
ORDER BY T.PlayerID, Avg(T.Score) DESC;

Thanks Allen for your reply - I am grateful for the reference to
subqueries which are definitely not understood by me. I will try to

[quoted text clipped - 69 lines]

 

[raskew via AccessMonster.com]

Have you tested, or is this 'air-code'>

Allen -

Not sure why but couldn't get your solution to work as published. The
following modification however does:

SELECT
Table1.PlayerID
, Avg(Table1.Score) AS AvgOfScore
FROM
Table1
WHERE
(((Table1.ID) In (
SELECT
Top 2 ID
FROM
Table1 AS Dupe
WHERE
Dupe.PlayerID = Table1.PlayerID
ORDER BY
Dupe.Score DESC
, Dupe.ID)))
GROUP BY
Table1.PlayerID;

Bob

Karl Dewey posted an alternative.
Sometimes posts dont' show up, so just in case you didn't see his post:

[quoted text clipped - 13 lines]

 

[Allen Browne]

I made no attempt to test Karl's suggestion.

My own suggestion was aircode (since I don't have these tables.)

In general, the suggestions you get in the newsgroups are just examples that
illustrate the path you need to take. You regularly find you have to
substitute your own table and field names and adjust the statement (e.g. if
the brackets didn't match.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Have you tested, or is this 'air-code'>

Allen -

Not sure why but couldn't get your solution to work as published. The
following modification however does:

SELECT
Table1.PlayerID
, Avg(Table1.Score) AS AvgOfScore
FROM
Table1
WHERE
(((Table1.ID) In (
SELECT
Top 2 ID
FROM
Table1 AS Dupe
WHERE
Dupe.PlayerID = Table1.PlayerID
ORDER BY
Dupe.Score DESC
, Dupe.ID)))
GROUP BY
Table1.PlayerID;

Bob

Karl Dewey posted an alternative.
Sometimes posts dont' show up, so just in case you didn't see his post:

[quoted text clipped - 13 lines]

3 8
4 5

 

[raskew via AccessMonster.com]

Thank you sir,

"Test then publish? A foreign concept?"

Best wishes, Bob

I made no attempt to test Karl's suggestion.

My own suggestion was aircode (since I don't have these tables.)

In general, the suggestions you get in the newsgroups are just examples that
illustrate the path you need to take. You regularly find you have to
substitute your own table and field names and adjust the statement (e.g. if
the brackets didn't match.)

Have you tested, or is this 'air-code'>

[quoted text clipped - 28 lines]