How to Calculate Average Sales Per Day in MySQL

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

Average daily sales is a good metric to understand how much revenue or orders to expect on a day-to-day basis. Here’s how to calculate average sales per day in MySQL, if your sales data is stored in a database, which is typically the case with online stores and mobile apps.

Table of Contents

How to Calculate Average Sales Per Day in MySQL

Let’s say you have the following sales table as shown below.

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-01-05',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-01-05 |   20 |
+------------+------+

You can easily calculate the average sales per day in MySQL with the following query, using the AVG function.

mysql> select avg(sale) from sales;
+-----------+
| avg(sale) |
+-----------+
|   22.0000 |
+-----------+

Let’s say you want to calculate average sales per day only for the month of December, 2019. You can do that by simply adding a WHERE clause in your query, as shown below. It will filter the data based on the WHERE condition, before calculating average.

mysql> select avg(sale) from sales 
where order_date>'2019-12-01' and order_date<'2020-01-01';

Similarly, you can also calculate average sales per week in MySQL.

Bonus Read : How to calculate total sales per month

Let’s say have sales data for multiple products in one table, as shown below.

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

mysql> insert into sales values('A','2020-01-01',20),('B','2020-01-02',25),
('B','2020-01-03',15),('A','2020-01-04',30),('A','2020-01-05',20);

mysql> select * from sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   20 |
| B       | 2020-01-02 |   25 |
| B       | 2020-01-03 |   15 |
| A       | 2020-01-04 |   30 |
| A       | 2020-01-05 |   20 |
+---------+------------+------+

If you want to calculate average sales per day for each product, then here’s an SQL query for it. In this case, you need to GROUP BY product column

mysql> select product, avg(sale) from sales group by product;
+---------+-----------+
| product | avg(sale) |
+---------+-----------+
| A       |   23.3333 |
| B       |   20.0000 |
+---------+-----------+

If you want to show these products in separate columns, you need to create a pivot table

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

How to Calculate Average Sale Per Day for Each Day of the Week

Let’s say you want to calculate average sale for each day of the week, as shown below

 Days       Average
 Monday       12.5         
 Tuesday      20.2                   
 Wednesday    30.5

You can do this simply using the DAYNAME function in MySQL, as shown below.

mysql> SELECT   DAYNAME(order_date), AVG(sale)
    -> FROM     sales
    -> GROUP BY DAYNAME(order_date);
+---------------------+-----------+
| DAYNAME(order_date) | AVG(sale) |
+---------------------+-----------+
| Friday              |   15.0000 |
| Saturday            |   30.0000 |
| Sunday              |   20.0000 |
| Thursday            |   25.0000 |
| Wednesday           |   20.0000 |
+---------------------+-----------+

After you calculate average sales per day, you can use a reporting tool to plot it in a bar chart or dashboard. Here’s an example of a bar chart created using Ubiq.