MySQL Query To Get Top Selling Products

It is important to get top selling products, if you run an online store or ecommerce business. Since most online websites run on MySQL, in this article, we will look at MySQL Query to get top selling products for your business. It s very useful in understanding which products are doing well, and which ones need a push. You can use these queries to get highest sold products or get a list of best selling items on your site.

MySQL Query To Get Top Selling Products

We will look at two cases – first, when your product information and sales information are in same table. Second, when your product and sales information are in different tables.

How to get best selling products

Let us say you have a table sales(id, product, order_date, amount) which contains information of product and sale amount in a single table.

mysql> create table sales(id int, 
        product varchar(10), 
        order_date date, 
        amount int);

mysql> insert into sales(id, product, order_date, amount)
       values(1, 'Bike','2021-01-01',150),
       (2, 'Car','2021-01-01',450),
       (3, 'Bicycle','2021-01-02',50),
       (5, 'Car','2021-01-02',450),
       (6, 'Bike','2021-01-03',150),
       (7, 'SUV','2021-01-04',850),
       (8, 'Bike','2021-01-04',150);

mysql> select * from sales;
+------+---------+------------+--------+
| id   | product | order_date | amount |
+------+---------+------------+--------+
|    1 | Bike    | 2021-01-01 |    150 |
|    2 | Car     | 2021-01-01 |    450 |
|    3 | Bicycle | 2021-01-02 |     50 |
|    5 | Car     | 2021-01-02 |    450 |
|    6 | Bike    | 2021-01-03 |    150 |
|    7 | SUV     | 2021-01-04 |    850 |
|    8 | Bike    | 2021-01-04 |    150 |
+------+---------+------------+--------+

We will get top selling product by sales, and by volume.

Bonus Read : How to Get Multiple Counts in MySQL

Top selling products by total sales

Here is the SQL query to get top selling products by total sales. In our case, we will select top 3 best selling products by total sales amount.

mysql> select product, sum(amount) 
       from sales 
       group by product 
       order by sum(amount) desc 
       limit 3;
 +---------+-------------+
 | product | sum(amount) |
 +---------+-------------+
 | Car     |         900 |
 | SUV     |         850 |
 | Bike    |         450 |
 +---------+-------------+

Let us look at the above query in detail. We basically GROUP BY product column and aggregate amount column using SUM function.

We further ORDER BY sum(amount) in descending order so that the top selling products are listed at the top. We also use LIMIT 3 clause to select only the top 3 rows of result.

Also read : How to Use CASE statement in MySQL

Top Selling Products by Total Volume

Here is the SQL query to get top selling products by total volume, that is, count of sales.

mysql> select product, count(amount) 
       from sales 
       group by product 
       order by count(amount) 
       desc limit 3;
 +---------+---------------+
 | product | count(amount) |
 +---------+---------------+
 | Bike    |             3 |
 | Car     |             2 |
 | Bicycle |             1 |
 +---------+---------------+

Now, we will look at second case. Sometimes, product and sales information are present in different tables.

Let us say you have two tables product(id, product_name) and sales(id, product_id, order_date, amount)

mysql> create table product(id int,product_name varchar(10));

mysql> insert into product(id, product_name)
      value(1,'Bike'),
      (2,'Car'),
      (3,'Bicycle'),
      (4,'SUV');

mysql> select * from product;
 +------+--------------+
 | id   | product_name |
 +------+--------------+
 |    1 | Bike         |
 |    2 | Car          |
 |    3 | Bicycle      |
 |    4 | SUV          |
 +------+--------------+

mysql> create table sales(id int, 
      product_id int, 
      order_date date, 
      amount int);

mysql> insert into sales(id, product_id, order_date, amount)
      values(1, 1,'2021-01-01',150),
      (2, 2,'2021-01-01',450),
      (3, 3,'2021-01-02',50),
      (5, 2,'2021-01-02',450),
      (6, 1,'2021-01-03',150),
      (7, 4,'2021-01-04',850),
      (8, 1,'2021-01-04',150);

mysql> select * from sales;
 +------+------------+------------+--------+
 | id   | product_id | order_date | amount |
 +------+------------+------------+--------+
 |    1 |          1 | 2021-01-01 |    150 |
 |    2 |          2 | 2021-01-01 |    450 |
 |    3 |          3 | 2021-01-02 |     50 |
 |    5 |          2 | 2021-01-02 |    450 |
 |    6 |          1 | 2021-01-03 |    150 |
 |    7 |          4 | 2021-01-04 |    850 |
 |    8 |          1 | 2021-01-04 |    150 |
 +------+------------+------------+--------+

Bonus Read : How to Use COALESCE in MySQL

Top selling products by total sales

Here is the SQL query to get top selling products by total sales. We will basically join the two tables to get product name and total sales in query result.

mysql> select product_name, sum(amount)
           from sales,product
           where sales.product_id=product.id
           group by product_name
           order by sum(amount) desc
           limit 3;
 +--------------+-------------+
 | product_name | sum(amount) |
 +--------------+-------------+
 | Car          |         900 |
 | SUV          |         850 |
 | Bike         |         450 |
 +--------------+-------------+

Let us look at the above query in detail. We SELECT product_name and sum(amount) columns from product and sales tables. We join the two tables using product_id column from sales table and id column from product table, GROUP BY product column and aggregate amount column using SUM function.

We further ORDER BY sum(amount) in descending order so that the top selling products are listed at the top. We also use LIMIT 3 clause to select only the top 3 rows of result.

Bonus Read : How to Query JSON column in MySQL

Top selling products by total volume

Here is the SQL query to get top selling products by total volume, that is, count of sales.

mysql> select product_name, count(amount)
           from sales,product
           where sales.product_id=product.id
           group by product_name
           order by count(amount) desc
           limit 3;
 +--------------+---------------+
 | product_name | count(amount) |
 +--------------+---------------+
 | Bike         |             3 |
 | Car          |             2 |
 | Bicycle      |             1 |
 +--------------+---------------+

Get top selling products using Ubiq

Ubiq Reporting tool supports all the above SQL queries and makes it easy to visualize SQL results in different ways. It also allows you to create dashboards & charts from MySQL data. Here is the SQL query to get top selling products, in Ubiq.