Averaging data and finding mode / most common entry

[dowzer]

I am total newbie to PHP so apologies for dumb questions.

After a lot of searching online, tearing my hair out and trial and error I have managed to set up an HTML form and a database which then accepts the inputs from that form.

I now would like to be able to display some summary statistics from the form data.

Simplistically, I have a table with the following data in it:

NAME SCORE1 SCORE2 SCORE 3
John 1 2 3
Philip 3 5 6
Paul 3 2 3
Peter 7 3 2
Mike 2 2 3

I would like to be able to find the average of column SCORE1 (3.2) and then the number which occurs most often in the other columns (the mode - 2 and 3 respectively). All the score fields have a type of INT.

Can anyone help me please to code this so I can simply display the average of score1 and then the mode of score2 and the mode of score3 in a browser something like below:

The average of SCORE1 is 3.2.
The mode of SCORE2 is 2.
The mode of SCORE3 is 3.

Thank you

Jason

[laserlight]

You may be able to use 3 SQL statements on table foo:

SELECT AVG(score1) FROM foo

SELECT score2, COUNT(score2) AS score_count FROM foo GROUP BY score2 ORDER BY score_count DESC LIMIT 1

SELECT score3, COUNT(score3) AS score_count FROM foo GROUP BY score3 ORDER BY score_count DESC LIMIT 1

If you are unable to use the LIMIT clause, don't worry, you can just read the first row of the result set and then discard the rest.

By the way, instead of having score1, score2, score3, etc columns, you should have a separate Score table.