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 that does that. (I'd only use the select part the GROUP BY looks suspect). — Jim
– Jim, Commented Oct 24, 2014 at 14:20
This doesn't work. Could it be because my numerical values are all decimals between 0 and 1, not integers? — mcplums
– mcplums, Commented Oct 24, 2014 at 15:51

Benvorth · Accepted Answer · 2014-10-25 10:16:33Z

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, 2014 at 10:16

answered Oct 24, 2014 at 15:00

Benvorth

7,8028 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mcplums Over a year ago

This doesn't work. Could it be because my numerical values are all decimals between 0 and 1, not integers?

Benvorth Over a year ago

For decimal values do eg a ... (numeric_value DIV 0.1) * 0.1 as bucket ...

Collectives™ on Stack Overflow

MySQL Histogram- changing bucket size

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related