# 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.

## 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

## 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.

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