How to create MySQL histogram type buckets based on column value

Question

I have the following table and I am trying to create histogram style buckets through MySQL.

| Id       | Values    |
| -------- | --------- |
| 1        | 5         |
| 2        | 7         |
| 3        | 9         |
| 4        | 11        |
| 5        | 15        |
| 6        | 31        |
| 7        | 32        |
| 8        | 43        |

What I am trying to achieve is as following:

| bucket   | count     |
| -------- | --------- |
| 0-9      | 3         |
| 10-19    | 2         |
| 20-29    | 0         |
| 30-39    | 2         |
| 40-49    | 1         |

Does anyone know how we can get this in a clean way?

I am relatively new to SQL querying. I was not able to achieve it. — Rachit Raut
– Rachit Raut, Commented Apr 1, 2021 at 5:52

FanoFN · Accepted Answer · 2021-04-01 08:24:56Z

1

One possible way is to create a reference table for the bucket list then LEFT JOIN it with your table. Try the following steps.

Create a table bucket_list for example:

CREATE TABLE bucket_list (
 id INT NOT NULL AUTO_INCREMENT,
 startno INT,
 endno INT,
 PRIMARY KEY(id));

Insert values into bucket_list:

 INSERT INTO bucket_list (startno, endno)
VALUES
  (0, 9),
  (10, 19),
  (20, 29),
  (30, 39),
  (40, 49),
  (50, 59),
  (60, 69),
  (70, 79);

Create a query to return expected result:

SELECT CONCAT(a.startno,'-',a.endno) AS bucket,
       SUM(CASE WHEN b.val IS NULL THEN 0 ELSE 1 END) AS COUNT
FROM bucket_list a 
LEFT JOIN mytable b ON b.val BETWEEN a.startno AND a.endno
GROUP BY bucket;

Here's a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7fee426efa2b1f1e39377bb7beb68b62

answered Apr 1, 2021 at 8:24

FanoFN

7,1442 gold badges16 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

How to create MySQL histogram type buckets based on column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related