SQL Query to Compare Product Sales By Month

Many times, you’ll need to compare product sales by month, side-by-side, to determine how they are doing. Here’s an SQL query to compare product sales by month, if your data is stored in MySQL/PostgreSQL/SQL Server databases.

SQL Query to Compare Product Sales By Month

Let’s say you have a table sales(order_date, product, sale) that contains daily sales of all your products.

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

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

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

SQL Query to compare product sales by month

In most cases, you will have sales rows for all your products one below the other, in separate rows, instead of separate columns. If you want to compare product sales by month, you need to pivot this data by product column.

Here’s the query to pivot your sales table by product column. We will look at it in detail

mysql> SELECT date_format(order_date,'%b-%y') as order_date,
       sum(IF(product='Pen', sale, NULL)) AS Pen,
       sum(IF(product='Paper', sale, NULL)) AS Paper
       FROM sales
       GROUP BY year(order_date),month(order_date),date_format(order_date,'%b-%y')
       ;
+------------+------+-------+
| order_date | Pen  | Paper |
+------------+------+-------+
| Jan-20     |  200 |   320 |
| Feb-20     |  250 |   220 |
| Mar-20     |  230 |   290 |
| Apr-20     |  190 |   210 |
| May-20     |  210 |   230 |
| Jun-20     |  320 |   120 |
| Jul-20     |  330 |   220 |
| Aug-20     |  210 |   260 |
| Sep-20     |  120 |   220 |
| Oct-20     |  280 |   120 |
| Nov-20     |  290 |   280 |
| Dec-20     |  200 |   320 |
+------------+------+-------+

In the above query, we use DATE_FORMAT function to get month names from order_date column. Further we use IF statement to create pivot table by product column. In this case, for each row, the IF statement will check product column’s value and add the sale value to either column labelled ‘Pen’ or ‘Paper’ depending on the product name. We also GROUP BY month name, to get monthly product sales, from daily orders.

After you calculate monthly sales per product, you can use a charting tool to plot it on a line chart, as shown below. Here’s an example of a line chart created using Ubiq.