Applying aggregate function to whole table

Here is my problem and I suspect this is a simple question for the
experts.
My query attempts to get the names of players that played in a game
during the past x minutes and then the average scores of that player
for ALL games played by that player.
The query I have right now looks something like this (its from an
Access query but you get the point):
SELECT players.name, count(game.gameno) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
game_players.gameno) ON players.playerno = game_players.playerno
WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
Now()))
As you see the Avg function will only return the average scores in the
games played that meet the "WHERE" condition. How can I get the
average score to be the Average for ALL scores for that player?
Thanks in advance.youretoast@.gmail.com,
This "select" statement is not related to sql server. There is not function
"now" in T-SQL, so I do not know if this can help. The idea is calculating
the avg for all rows and just counting the ones that meet the predicate.
SELECT
players.name,
count(
case when game.date_played Between DateAdd('n',-100000,Now()) And Now()
then game.gameno end
) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM
players
INNER JOIN
(
game
INNER JOIN
game_players
ON game.gameno = game_players.gameno
) ON players.playerno = game_players.playerno
group by
players.name
AMB
"youretoast@.gmail.com" wrote:

> Here is my problem and I suspect this is a simple question for the
> experts.
> My query attempts to get the names of players that played in a game
> during the past x minutes and then the average scores of that player
> for ALL games played by that player.
> The query I have right now looks something like this (its from an
> Access query but you get the point):
> SELECT players.name, count(game.gameno) as gamesplayed,
> Avg(game_players.score) AS avgscore
> FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
> game_players.gameno) ON players.playerno = game_players.playerno
> WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
> Now()))
> As you see the Avg function will only return the average scores in the
> games played that meet the "WHERE" condition. How can I get the
> average score to be the Average for ALL scores for that player?
> Thanks in advance.
>