How to Create Histograms with Predefined Bins in MySQL?

Creating Histograms with Predefined Bins in MySQL

To obtain data intended for plotting histograms, users must often group numeric values into predefined bins. SQL queries can accomplish this task, eliminating the need for additional scripting.

Consider the query:

<code class="sql">select total, count(total) from faults GROUP BY total;</code>

However, this query generates numerous rows. To group data into bins, follow these steps:

1. Round the numeric value to the desired bin size using the ROUND() function with a negative offset. For example, to create bins of size 10, use: ROUND(total, -1).
2. Group the rounded values using the GROUP BY clause:

<code class="sql">SELECT ROUND(total, -1) AS bin, COUNT(*) AS count
FROM faults
GROUP BY bin;</code>

This query groups total values into bins of size 10 and returns the count for each bin.

Example:

Consider the table faults with the following data:

total	count
30	1
31	2
33	1
34	3
35	2
36	6
37	3
38	2
41	1
42	5
43	1
44	7
45	4
46	3
47	2
49	3
50	2
51	3
52	4
53	2
54	1
55	3
56	4
57	4
58	2
59	2
60	4
61	1
63	2
64	5
65	2
66	3
67	5
68	5

The following query will group the values into bins of size 10:

<code class="sql">SELECT ROUND(total, -1) AS bin, COUNT(*) AS count
FROM faults
GROUP BY bin;</code>

Output:

bin	count
30	23
40	15
50	51
60	45

The above is the detailed content of How to Create Histograms with Predefined Bins in MySQL?. For more information, please follow other related articles on the PHP Chinese website!