How to Calculate Total Sales Per Month in MySQL?

Last updated on July 16th, 2024 at 05:48 am

Month wise sales is a very useful business KPI metric to track, for every business. It helps you understand how your marketing campaigns & initiatives impact your sales, identify sales periodicity and spot fluctuations. If your data is stored in a database, you can easily calculate monthly sales report in SQL. Here’s how to calculate total sales per month in MySQL. You can also use it to get month wise data in MySQL.

Table of Contents

How to Calculate Total Sales Per Month in MySQL?

Here are the steps to calculate total sales per month in MySQL. Let’s say you have the following table sales(order_date, sale) that stores sales data.

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 Monthly Sales Report in MySQL

MySQL provides MONTH and YEAR functions that allow you to calculate month and year respectively, from a date. They are very useful to aggregate data by month in MySQL. We will use these 2 functions to calculate total sales per month in MySQL.

mysql> select year(order_date),month(order_date),sum(sale)
     from sales
     group by year(order_date),month(order_date)
     order by year(order_date),month(order_date);
+------------------+-------------------+-----------+
| year(order_date) | month(order_date) | sum(sale) |
+------------------+-------------------+-----------+
|             2020 |                 1 |       408 |
|             2020 |                 2 |       320 |
|             2020 |                 3 |       540 |
|              ... |               ... |       ... |
+------------------+-------------------+-----------+

The above query uses SUM function which will help you sum the total sales every month. If you only want a total count of sales every month, then you can use COUNT function instead.

mysql> select year(order_date),month(order_date),count(sale)
     from sales
     group by year(order_date),month(order_date)
     order by year(order_date),month(order_date);
+------------------+-------------------+-----------+
| year(order_date) | month(order_date) | sum(sale) |
+------------------+-------------------+-----------+
|             2020 |                 1 |        18 |
|             2020 |                 2 |        10 |
|             2020 |                 3 |        21 |
|              ... |               ... |       ... |
+------------------+-------------------+-----------+

If you want to filter the data used to calculate total sales per month in MySQL, then you can do so with the help of WHERE clause, as shown below in bold

mysql> select year(order_date),month(order_date),sum(sale)
     from sales
     WHERE condition
     group by year(order_date),month(order_date)
     order by year(order_date),month(order_date);

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

If you have sales data for multiple products in your table sales(product, order_date, sale), then you can calculate monthly sales for each product, by updating the GROUP BY and ORDER BY clauses

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

mysql> select product, year(order_date),month(order_date),sum(sale)
          from sales
          group by product, year(order_date),month(order_date)
          order by product, year(order_date),month(order_date);
+---------+------------------+-------------------+-----------+
| product | year(order_date) | month(order_date) | sum(sale) |
+---------+------------------+-------------------+-----------+
| A       |             2020 |                 1 |        70 |
| B       |             2020 |                 1 |        40 |
| ...     |              ... |               ... |       ... |
+---------+------------------+-------------------+-----------+

Now that you know how to calculate total sales per month in MySQL, you can customize it as per your requirement.

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

To chart monthly sales data, we will slightly modify our earlier SQL query to get Month names using DATE_FORMAT function, instead of using year and month numbers

mysql> select date_format(order_date,'%M'),sum(sale)
      from sales
      group by year(order_date),month(order_date)
      order by year(order_date),month(order_date);
+------------------------------+-----------+
| date_format(order_date,'%M') | sum(sale) |
+------------------------------+-----------+
| January                      |       408 |
| Febuary                      |       320 |
| March                        |       540 |
| ...                          |       ... |
+------------------------------+-----------+

After you calculate total sales per month in MySQL, you can plot monthly sales on a bar chart using a charting tool. Here’s an example of monthly sales graph created using Ubiq