How to create a histogram using MySQL

Question

I am trying to create histogram data using the following query:

SELECT FLOOR(Max_Irrad/10) AS bucket, COUNT(*) AS COUNT FROM marctest.test_summarynimish where Lcu_name='Allegro' and Lcu_Mode='Standard' GROUP BY bucket; 

Below is the result that I get:

 bucket count 0 3 4 3 5 12 7 6 8 3 10 3 

now the bucket field is the range or bit used in the histogram. I want to create bucket values ​​with a constant range, e.g. starting at 0,4,8,12 .... etc. Is there a way to achieve this in mysql? This is how I expect to get the result:

  bucket count 0 3 4 21 8 6 

Answer 1

I think we can use the following general form to create a general histogram:

 select (x div 4) * 4 as NewX, count(*) as NewY from histogram group by NewX 

Where x is the true x-axis value of x, and count(*) is the actual y-value. The number 4 is the size of the quantity x that we want to group. This means that we will group all the x values ​​in groups of 4 (for example: group 1 is 0, 1, 2, 3, group 2 is 4, 5, 6, 7, etc.). The amount of each item in the group will become NewY

You can play with it here

Applying this logic to your query, it will be:

 select (floor(Max_Irrad/10) div 4) * 4 as NewX, count(*) as NewY from marctest.test_summarynimish where Lcu_name='Allegro' and Lcu_Mode='Standard' group by NewX 

Let me know if you have any problems or doubts about this.

Answer 2

Just make your buckets large by dividing Max_Irrad by 40 instead of 10.