How to Calculate Average Sales Per Week in MySQL

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

Average Sales Per Week is a useful metric for every business, no matter which industry you work in. It gives a good idea of how your business doing, and over a reasonable time frame. It also helps smoothen out the noise present day-to-day sales numbers. Here’s how to calculate average sales per week in MySQL.

Table of Contents

How to Calculate Average Sales Per Week in MySQL

There are multiple ways to calculate average sales per week in MySQL. We will look at each of them

Let’s say you have table with daily sales data 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 |
| ...        |   ...|
+------------+------+

Calculate Average Sales Per Week in MySQL

MySQL provides a built-in function WEEK to that return the WEEK number of the year. You can easily calculate average sales per week in MySQL using GROUP BY clause and AVG function, as shown below.

mysql> select week(order_date),avg(sale) from sales
    -> group by week(order_date);
+------------------+-----------+
| week(order_date) | avg(sale) |
+------------------+-----------+
|                0 |   12.0000 |
|                1 |   11.5714 |
|                2 |   13.5714 |
|                3 |   15.8571 |
|                4 |   14.6000 |
+------------------+-----------+

You can beautify your result by using CONCAT and ROUND functions – CONCAT can be used to concatenate custom strings to week numbers, and ROUND function can be used to round average values. Here’s an example,

mysql> select concat('Week ',week(order_date)) as 'Week',round(avg(sale),2) as 'Average Sale'
 from sales group by week(order_date);
+--------+--------------+
| Week   | Average Sale |
+--------+--------------+
| Week 0 |        12.00 |
| Week 1 |        11.57 |
| Week 2 |        13.57 |
| Week 3 |        15.86 |
| Week 4 |        14.60 |
+--------+--------------+

Bonus Read : How to Calculate Percentage Growth Month By Month in MySQL

If your table has more columns, whose averages you want to calculate, you can simply add them to your query as shown below in bold

mysql> select concat('Week ',week(order_date)) as 'Week',round(avg(sale),2) as 'Average Sale'
,round(avg(goal),2) as 'Average Goals',round(avg(revenue),2) as 'Average Revenue'
 from sales group by week(order_date);

If you want to add filters to your query, you can add a WHERE clause to your query as shown below

mysql> select week(order_date),avg(sale) from sales WHERE <condition here>
    -> group by week(order_date);

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

MySQL’s Week function provides multiple ways to calculate weekly data. Its syntax is

WEEK(date, firstdayofweek)

firstdayofweek is optional, and can take the following values

Option value	First day of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with more than 3 days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with more than 3 days this year
7	Monday	1-53	with a Monday in this year

Default value is 0.

When you calculate average sales per week in MySQL, be careful while using the second argument in WEEK function. If not used properly, you might end up miscalculating average numbers.

Finally, you can use a reporting tool to plot average sales per week on a dashboard and share it with your team. Here’s an example of bar chart showing average sales per week, created using Ubiq.