cluster and graph data with php and mysql

Objective: Create a graph to show where the greatest cluster of scores are.

I have a table with thousands of rows and the following columns: id | matchId | username | score

All scores are between 1 and 5.

I want to create an array where that lumps scores together in the nearest tenth, so if I have the follow scores (1, 1.01, 1.113, 1.1, 1.1, 1.25, 1.23, 1.3) my array should look like this (2, 3, 2, 1) where we have 2 entries that are >= 1 and <1.1, 3 that are >= 1.1 and <1.2, 2 that are >=1.2 and < 1.3, etc. etc.

I want to then use this data to plot points on an X/Y graph to give me the distribution of data, where the X axis will b开发者_运维百科e the clustered scores, and the Y axis will be how many entries there are for those.

I should note, that there will be many scores for a given match, but I can use the AVG(score) and group by matchId to create an array that gives an averaged score for each match, so I don't think this will be an issue.

Thank you.

You can solve this by grouping and counting after performing standard rounding. See below:

CREATE TABLE avgtenths(val FLOAT);
INSERT INTO avgtenths VALUES (1), (1.01), (1.113), (1.1), (1.1), (1.25), (1.23), (1.3);

SELECT ROUND(val,1) r, COUNT(*) c FROM avgtenths GROUP BY r;

This should yield the following result:

+------+---+
| r    | c |
+------+---+
|  1.0 | 2 |
|  1.1 | 3 |
|  1.2 | 2 |
|  1.3 | 1 |
+------+---+

The trick here is the round function, which accepts a second parameter for the number of decimal places: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_round