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.

compare product sales by month

 

Did you know you can create pivot tables in Ubiq using just drag & drop?

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

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!