problem for calculating average of %age of numbers

[Detrusor]

Hello

I've got a SQL table that contains times (in seconds, which corresponds to Minesweeper games 😃).

I would like to get the average time, but with considerating only the 30 % best games...
I did a first request which order the times ascending, and which search the number of the line that contain the "30%th" game (for example, if the table contain 500 entries, the "30%th" line is the #150)

$req = query ( "SELECT times FROM stats WHERE id_player = '$i' ORDER BY times ASC" );
$nb = round (0.3 * mysql_num_rows($req),0);

(NB : this 1st request works fine; it gives the good number)

Then i want to use that $nb in a second request which calculates the average :

$avg = query_result( "SELECT AVG(times) FROM stats WHERE id_player = '$i' ORDER BY times ASC LIMIT '$nb'");

The problem is that this request gives the average of the whole games, instead of considerating the first $nb games...
I thought that the "ORDER BY temps ASC LIMIT '$nb'" would be enough to consider only those games :rolleyes:

Do you have any good idea to solve that?
Thanks a lot 😉

PS: 1. 'query' & 'query_results' are functions that are declared before in the code
2. '$i' refers to a player id. This average code is supposed to occur several times, for each player

[drawmack]

Your best bet is probably to query for the sum with the limit of 0,$nb then just divide that sum by $nb and you should have what you're looking for.

[Detrusor]

thanks, drawmack, but this doesn't change the problem... If i consider the sum, whatever i put for the LIMIT, it gives me the overral sum instead of considering only the first 30%...

[drawmack]

I was saying:

Select <column> from <Table> where <whatever> limit 0,$end

Then loop through and add them in php. After that just take the total over $end and you'll have what you want.