CODEWITHSUNDEEP
mysqlsqlmysql-5.5
Raphael
Raphael

Reputation: 112

Percentage growth month by month - MySQL 5.x

I have a table sales with some columns and data like this:

SELECT order_date, sale 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 |
| 2020-03-10 |   40 |
| 2020-04-01 |   20 |
| 2020-04-02 |   25 |
| 2020-04-03 |   10 |
+------------+------+

and I would like to calculate, for example, monthly growth rate.

From the previous data example the expected result would be like this:

month  sale  growth_rate  

     1    90            0         
     2    65       -27.78          
     3   105        61.54         
     4    55       -47.62

We have an old MySQL version, 5.x.

could anyone help or give me some clues to achieve this?

Upvotes: 0

Views: 1591

Answers (3)

Chema
Chema

Reputation: 2838

A little tricky for me, but I think the code below works as expected

SELECT month, sale,growth_rate
FROM(
SELECT month, sale, 
       IF(@last_entry = 0, 0, ROUND(((sale - @last_entry) / @last_entry) * 100,2)) AS growth_rate,
       @last_entry := sale AS last_entry                  
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) t;

expected result

+-------+------+-------------+
| month | sale | growth_rate |
+-------+------+-------------+
|     1 |   90 |        0.00 |
|     2 |   65 |      -27.78 |
|     3 |  105 |       61.54 |
|     4 |   55 |      -47.62 |
+-------+------+-------------+

Upvotes: 0

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

It is a bit complicate:

select 
   s.*
   -- calculate rate
   , ifnull(round((s.mnt_sale - n.mnt_sale)/n.mnt_sale * 10000)/100, 0) as growth_rate
from ( 
    -- calculate monthly summary
    select month(order_date) mnt, sum(sale) mnt_sale
    from sales
    group by mnt
) s
left join ( -- join next month summary
    -- calculate monthly summary one more time
    select month(order_date) mnt, sum(sale) mnt_sale
    from sales
    group by mnt) n on n.mnt = s.mnt - 1
;

DB Fiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use aggregation and window functions. Something like his:

select year(order_date) as year, month(order_date) as month, sum(sale) as sale,
       100 * (1 - sum(sale) / lag(sum(sale), 1, sum(sale)) over (order by min(order_date)) as growth_rate
from t
group by year, month

Upvotes: 1

Related Questions