MySQL Histogram- changing bucket size

Question

I have some code borrowed from a previous stackoverflow post which makes it easy to create histogram data out of a large table. I repeat it below:

SELECT ROUND(numeric_value, -2)    AS bucket,
       COUNT(*)                    AS COUNT,
FROM   my_table
GROUP  BY bucket;

[I removed references to the BAR column as I don't need it.] The problem is it is only possible to vary the bucket size by one order of magnitude (by changing the ROUND attribute), how would I change the code to enable me to change the bucket size with more flexibility? For example, if I wanted to state the numerical size of each bucket?

Thanks!

The post you linked has an [answer](http://stackoverflow.com/a/7410878/505722) that does that. (I'd only use the select part the GROUP BY looks suspect). — Jim, Oct 24 '14 at 14:20
This doesn't work. Could it be because my numerical values are all decimals between 0 and 1, not integers? — mcplums, Oct 24 '14 at 15:51

Benvorth · Answer 1 · 2014-10-25T10:16:33.857

0

E.g. for a 0.2-step bucket do a

SELECT 
    (numeric_value DIV 0.2) * 0.2 as bucket,
    Count(*)
FROM 
    mytable
GROUP BY 1

edited Oct 25 '14 at 10:16

answered Oct 24 '14 at 15:00

Benvorth

7,416
8
49
70

This doesn't work. Could it be because my numerical values are all decimals between 0 and 1, not integers? – mcplums Oct 24 '14 at 15:51
For decimal values do eg a `... (numeric_value DIV 0.1) * 0.1 as bucket ...` – Benvorth Oct 25 '14 at 10:15

MySQL Histogram- changing bucket size

1 Answers1