Returning the average amount of a column

Question

I have a table which represents the scores and id's of users who have played a flash game. I want to return the amount of times the average user has tried the game- every time they complete a "try" the score and id of the user is input into the mysql table. The structure is -

id int(11)                      
score int(11)                               
fb_id int(25)   

This gives me the amount of tries for every user

SELECT count(score) AS counted FROM `ifa_scores` GROUP BY `fb_id` 

but i want something like this

i would like the average amount of times played overall- ie the average amount of rows with the same FB_ID

Answer 1

SELECT `fb_id`, avg(score) AS counted FROM `ifa_scores` GROUP BY `fb_id` 

Answer 2

You'd need to get a count of how many games have been played, and a count of the number of players, join them together, and divide appropriately; something like

SELECT GAMES_PLAYED,
       PLAYERS,
       GAMES_PLAYED / PLAYERS AS AVERAGE_GAMES_PER_PLAYER
FROM
  (SELECT COUNT(*) AS GAMES_PLAYED FROM 'ifa_scores') g,
  (SELECT COUNT(*) AS PLAYERS FROM (SELECT DISTINCT 'fb_id' FROM 'ifa_scores')) p;

should work. Alternatively you could use

SELECT AVG(GAMES_PLAYED) AS AVERAGE_GAMES_PER_PLAYER
FROM (SELECT FB_ID, COUNT(*) AS GAMES_PLAYED
        FROM 'ifa_scores'
        GROUP BY 'fb_id');

Share and enjoy.

Answer 3

SELECT AVG(t.counted)
FROM
(
    SELECT COUNT(id) AS counted
    FROM ifa_scores
    GROUP BY fb_id
) AS t

Answer 4

The following should work: count(*) returns number of games played by each user. taking avg of this should give you the answer you are looking for. please check

SELECT fb_id, avg(count(*)) AS counted FROM ifa_scores GROUP BY fb_id