# How to Calculate Percentage Growth Week Over Week in MySQL

Monitoring Percentage Growth Week-over-week is a useful way to track weekly growth trends in your business. Here’s how to calculate percentage growth week over week in MySQL. It can be used to calculate week over week change in SQL for any metric.

## How to Calculate Percentage Growth Week Over Week in MySQL

Here are the steps to calculate percentage growth week over week in MySQL. Let’s say you have a table weekly_sales(week,sale) that contains weekly sales numbers, as shown below.

```mysql> create table weekly_sales(week int, sale int);

mysql> insert into weekly_sales(week,sale)
values(1,20),(2,30),(3,25),(4,45),(5,25);

mysql> select * from weekly_sales;
+------+------+
| week | sale |
+------+------+
|    1 |   20 |
|    2 |   30 |
|    3 |   25 |
|    4 |   45 |
|    5 |   25 |
+------+------+
```

You can calculate percentage growth week over week using the following SQL query for growth percentage.

```mysql> select week, 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 week, sum(sale) sale
from   weekly_sales
group by week) y;
+------+------+-------------+---------------------+
| week | 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 week, we store its previous week’s data in a temporary variable called last_entry and use it to calculate percentage growth.

However, in most cases, you will have daily sales data instead of weekly sales. So, in that case, you need to first aggregate it to weekly sales and then use the above query to calculate percentage growth week over week.

Let’s say you have a daily sales data table sales(order_date, sale)

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

mysql>insert into sales(order_date,sale)
values('2020-01-01',10),('2020-01-02',12),('2020-01-03',15),
('2020-01-04',11),('2020-01-05',13),('2020-01-06',9),
('2020-01-07',21),('2020-01-08',10),('2020-01-09',10),
('2020-01-10',2),('2020-01-11',16),('2020-01-12',12),
('2020-01-13',10),('2020-01-14',18),('2020-01-15',15),
('2020-01-16',12),('2020-01-17',10),('2020-01-18',18),
('2020-01-19',14),('2020-01-20',16),('2020-01-21',12),
('2020-01-22',21),('2020-01-23',13),('2020-01-24',15),
('2020-01-25',20),('2020-01-26',14),('2020-01-27',16),
('2020-01-28',15),('2020-01-29',10),('2020-01-30',18);

mysql>select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 |   10 |
| 2020-01-02 |   12 |
| 2020-01-03 |   15 |
| 2020-01-04 |   11 |
| 2020-01-05 |   13 |
| 2020-01-06 |    9 |
| 2020-01-07 |   21 |
| 2020-01-08 |   10 |
| 2020-01-09 |   10 |
| ...        |   ...|
+------------+------+
```

Let’s say you want to calculate percentage growth week over week, here’s the SQL for percent change over time.

```mysql> select week, 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 week, sum(sale) sale
from   (select week(order_date) as week,sum(sale) as sale
from sales group by week(order_date)) weekly_sales
group by week) y;
+------+------+-------------+---------------------+
| week | sale | growth rate | @last_entry := sale |
+------+------+-------------+---------------------+
|    0 |   48 |           0 |                  48 |
|    1 |   81 |       68.75 |                  81 |
|    2 |   95 |       17.28 |                  95 |
|    3 |  111 |       16.84 |                 111 |
|    4 |   73 |      -34.23 |                  73 |
+------+------+-------------+---------------------+

```

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

If you want to filter the data used for your query, you can add a WHERE clause in your query as shown below

```mysql> select week, 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 week, sum(sale) sale
from   (select week(order_date) as week,sum(sale) as sale
from sales
WHERE condition
group by week(order_date)) weekly_sales
group by week) y;

```

Please note, since the calculation of percentage growth week over week depends on storing previous row’s value in a temporary variable, please make sure that your table is already sorted in ascending order of week numbers. Else it might give wrong results.

After you calculate percentage growth week over week, you can use a charting tool to plot the result in a bar chart, and share it with your team. Here’s an example of bar chart that shows percent change over time, created using Ubiq.

If you want to create charts, dashboards & reports from MySQL database, you can try Ubiq. We offer a 14-day free trial.