How to Create Histogram in MySQL?

Last updated on September 1st, 2020 at 05:27 am

Histogram helps you plot frequency distribution from data, and draw insights quickly. Here’s how to create histogram in MySQL. You can use it to create frequency distribution in MySQL as well as PostgreSQL.

Table of Contents

How to Create Histogram in MySQL?

Here’s the SQL query to create histogram in MySQL.

Let’s say you have the following table sales(order_date, sale)

mysql> create table sales(order_date date,sale int);

mysql> insert into sales values('2020-04-01',210),
           ('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
           ('2020-04-05',200),('2020-04-10',220),('2020-04-06',250),
           ('2020-04-07',215),('2020-04-08',300),('2020-04-09',250);

mysql> select * from sales;
 order_date | sale
------------+------
 2020-04-01 |  210
 2020-04-02 |  125
 2020-04-03 |  150
 2020-04-04 |  230
 2020-04-05 |  200
 2020-04-10 |  220
 2020-04-06 |  250
 2020-04-07 |  215
 2020-04-08 |  300
 2020-04-09 |  250

Bonus Read : How to Import CSV File into MySQL

Create Histogram in MySQL

There are multiple MySQL Histrogram queries to create histogram in MySQL. We will look at each of them.

Simplest way to create Histogram in MySQL

If your MySQL Histogram bins have equal intervals (10-20, 20-30, 30-40, etc.) then here’s the SQL query to create frequency distribution in MySQL.

mysql> select floor(sale/10)*10 as bin_floor, count(*)
     from sales
     group by 1
     order by 1;
+-----------+----------+
| bin_floor | count(*) |
+-----------+----------+
|       120 |        1 |
|       150 |        1 |
|       200 |        1 |
|       210 |        2 |
|       220 |        1 |
|       230 |        1 |
|       250 |        2 |
|       300 |        1 |
+-----------+----------+

In the above query, the histogram bin size is 10. If you want to change the bin size to 25, change floor(sale/10)*10 to floor(sale/25)*25

mysql> select floor(sale/25)*25 as bin_floor, count(*)
     from sales
     group by 1
     order by 1;
+-----------+----------+
| bin_floor | count(*) |
+-----------+----------+
|       125 |        1 |
|       150 |        1 |
|       200 |        4 |
|       225 |        1 |
|       250 |        2 |
|       300 |        1 |
+-----------+----------+

The advantage of the above method is that it automatically changes the bins based on addition/deletion/modification of data.

Bonus Read : How to Calculate Conversion Rate in MySQL

Most Flexible Way to Create Histrogram in MySQL

If you already know the histogram bins to be created or if your histogram bucket sizes are different, then you can create frequency distribution using CASE statement. Here’s the Histogram query for MySQL

mysql> select "100-150" as TotalRange,count(sale) as Count from sales
        where sale between 100 and 150
     union (
     select "150-200" as TotalRange,count(sale) as Count from sales
        where sale between 150 and 200)
     union (
     select "200-225" as TotalRange,count(sale) as Count from sales
        where sale between 200 and 225)
     union (
     select "225-250" as TotalRange,count(sale) as Count from sales
        where sale between 225 and 250);
+------------+-------+
| TotalRange | Count |
+------------+-------+
| 100-150    |     2 |
| 150-200    |     2 |
| 200-225    |     4 |
| 225-250    |     3 |
+------------+-------+

In this query, you can change the histogram buckets to any range you want, they don’t need to have uniform intervals. This query works great if you have 5-10 buckets.

Bonus Read : How to Fill Missing Dates in MySQL

After you create frequency distribution in MySQL, you can use a charting tool to plot it in a column chart. Here’s a graph created using Ubiq.