Applying aggregate function to whole table

19 May 2006 1:50 PM

youretoast

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.

19 May 2006 2:43 PM

Alejandro Mesa

youreto***@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

Show quote

"youreto***@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.
>
>