How to Calculate Percentage Growth Month By Month in MySQL

Last updated on May 17th, 2020 at 06:27 am

Month over month growth is a key performance indicator for every business. Since, there is no function to calculate percentage growth month by month in MySQL, you need to write an SQL query to calculate month over month change. So let’s see how to calculate percentage growth month by month in MySQL. You can also use it to monitor month over month percent change over time in your business.

How to Calculate Percentage Growth Month By Month in MySQL

Let’s say you have data table month_sales(month,sale) that contains monthly sales, as shown below.

mysql> create table monthly_sales(month int,sale int);
mysql> insert into monthly_sales(month,sale) values(1,20),
(2,30),(3,25),(4,45),(5,25);
mysql> select * from monthly_sales;
+-------+------+
| month | sale |
+-------+------+
| 1     |   20 |
| 2     |   30 |
| 3     |   25 |
| 4     |   45 |
| 5     |   25 |
+-------+------+

You can calculate percentage growth month by month using the following SQL.

mysql> select month, sale,
    if(@last_entry = 0, 0, round(((sale - @last_entry) / @last_entry) * 100,2)) "growth rate",
    @last_entry := sale
    from
    (select @last_entry := 0) x,
    (select month, sum(sale) sale
    from   monthly_sales
    group by month) y;
+-------+------+-------------+---------------------+
| month | sale | growth rate | @last_entry := sale |
+-------+------+-------------+---------------------+
| 1     |   20 |           0 |                  20 |
| 2     |   30 |       50.00 |                  30 |
| 3     |   25 |      -16.67 |                  25 |
| 4     |   45 |       80.00 |                  45 |
| 5     |   25 |      -44.44 |                  25 |
+-------+------+-------------+---------------------+

In the above query, for each row(month), we store the sale of previous month in a temporary variable last_entry

Also read, How to Calculate Percentage Growth Week Over Week in MySQL

However, in most cases, you’ll have daily sales data. In that case, you need to first aggregate it to monthly sales, and then calculate percentage growth rate month by month. Let’s say you have a data table sales(created_at, sale) that contains daily sales information.

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

mysql> insert into sales values('2020-01-01',20),
('2020-01-02',25),('2020-01-03',15),('2020-01-04',30),
('2020-02-05',20),('2020-02-10',20),('2020-02-06',25),
('2020-03-07',15),('2020-03-08',30),('2020-03-09',20);

mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 |   20 |
| 2020-01-02 |   25 |
| 2020-01-03 |   15 |
| 2020-01-04 |   30 |
| 2020-02-05 |   20 |
| 2020-02-10 |   20 |
| 2020-02-06 |   25 |
| 2020-03-07 |   15 |
| 2020-03-08 |   30 |
| 2020-03-09 |   20 |
+------------+------+

Also read : How to Calculate Average Sales Per Week in MySQL

Let’s say you want to calculate monthly growth rate, here’s the SQL query for it.

mysql> select month, sale, 
       if(@last_entry = 0, 0, round(((sale - @last_entry) / @last_entry) * 100,2)) "growth rate",
       @last_entry := sale                  
from
      (select @last_entry := 0) x,
      (select month, sum(sale) sale
       from   (select month(order_date) as month,sum(sale) as sale 
               from sales group by month(order_date)) monthly_sales
       group by month) y;		

+-------+------+-------------+---------------------+
| month | sale | growth rate | @last_entry := sale |
+-------+------+-------------+---------------------+
|     1 |   90 |           0 |                  90 |
|     2 |   65 |      -27.78 |                  65 |
|     3 |   65 |        0.00 |                  65 |
+-------+------+-------------+---------------------+

In the above query, we first aggregate the daily sales into monthly values (shown in bold) and then use the earlier query to calculate percentage growth.

Bonus Read : How to Calculate Average Sales Per Day in MySQL

If you want to filter your data before calculating percentage growth month by month, you can do that by adding WHERE clause to your query as shown below

mysql> select month, sale, 
       if(@last_entry = 0, 0, round(((sale - @last_entry) / @last_entry) * 100,2)) "growth rate",
       @last_entry := sale                  
from
      (select @last_entry := 0) x,
      (select month, sum(sale) sale
       from   (select month(order_date) as month,sum(sale) as sale 
               from sales 
               WHERE condition
               group by month(order_date)) monthly_sales
       group by month) y;

Bonus Read : How to Calculate Total Sales Per Month in MySQL?

Please note: Since the calculation of percentage growth month by month is based on the concept of storing previous row’s value in a temporary variable, make sure that your table is sorted in ascending order of month (e.g 1,2,3,etc or 202001,202002,202003, etc.). Else your data might get sorted alphabetically and give wrong results when you calculate percentage growth month by month in MySQL.

You can use a reporting tool to plot this data on a bar chart or dashboard and share it with your team. Here’s an example of a bar chart that shows month over month percent sales growth, created using Ubiq.