How To Calculate Margin In MySQL

It is important to calculate profit for every business. If your website or online store runs on MySQL, then you can easily calculate margin in MySQL. In this article, we will look at how to calculate calculate margin in MySQL, calculate margin for each product and also calculate daily margin.

How To Calculate Margin In MySQL

Let us say you have two tables purchases(order_id, product_id, quantity, cost_price, order_date) and sales(order_id, product_id, quantity, selling_price, order_date) that contain details of each product’s purchase and sale information.

mysql> create table purchases(order_id int, 
            product_id int, 
            quantity int, 
            cost_price int, 
            order_date date);

mysql> insert into purchases(order_id, product_id,
      quantity, cost_price, order_date)
      values(1,101,10,30,'2021-01-01'),
      (2,102,15,50,'2021-01-02'),
      (3,101,30,80,'2021-01-03'),
      (4,103,20,35,'2021-01-04'),
      (5,105,10,50,'2021-01-05'),
      (6,104,17,40,'2021-01-06'),
      (7,101,30,45,'2021-01-07'),
      (8,102,60,60,'2021-01-08'),
      (9,110,19,70,'2021-01-09'),
      (10,108,20,80,'2021-01-10');

mysql> select * from purchases;
 +----------+------------+----------+------------+------------+
 | order_id | product_id | quantity | cost_price | order_date |
 +----------+------------+----------+------------+------------+
 |        1 |        101 |       10 |         30 | 2021-01-01 |
 |        2 |        102 |       15 |         50 | 2021-01-02 |
 |        3 |        101 |       30 |         80 | 2021-01-03 |
 |        4 |        103 |       20 |         35 | 2021-01-04 |
 |        5 |        105 |       10 |         50 | 2021-01-05 |
 |        6 |        104 |       17 |         40 | 2021-01-06 |
 |        7 |        101 |       30 |         45 | 2021-01-07 |
 |        8 |        102 |       60 |         60 | 2021-01-08 |
 |        9 |        110 |       19 |         70 | 2021-01-09 |
 |       10 |        108 |       20 |         80 | 2021-01-10 |
 +----------+------------+----------+------------+------------+

mysql> create table sales(order_id int, 
        product_id int, 
        quantity int, 
        selling_price int, 
        order_date date);

mysql> insert into sales(order_id, product_id, 
       quantity, selling_price, order_date)
       values(1,101,8,70,'2021-01-01'),
       (2,102,10,150,'2021-01-02'),
       (3,101,25,280,'2021-01-03'),
       (4,103,20,135,'2021-01-04'),
       (5,105,10,350,'2021-01-05'),
       (6,104,15,140,'2021-01-06'),
       (7,101,20,65,'2021-01-07'),
       (8,102,50,160,'2021-01-08'),
       (9,110,15,120,'2021-01-09'),
       (10,108,15,180,'2021-01-10');

mysql> select * from sales;
+----------+------------+----------+---------------+------------+
| order_id | product_id | quantity | selling_price | order_date |
+----------+------------+----------+---------------+------------+
|        1 |        101 |        8 |            70 | 2021-01-01 |
|        2 |        102 |       10 |           150 | 2021-01-02 |
|        3 |        101 |       25 |           280 | 2021-01-03 |
|        4 |        103 |       20 |           135 | 2021-01-04 |
|        5 |        105 |       10 |           350 | 2021-01-05 |
|        6 |        104 |       15 |           140 | 2021-01-06 |
|        7 |        101 |       20 |            65 | 2021-01-07 |
|        8 |        102 |       50 |           160 | 2021-01-08 |
|        9 |        110 |       15 |           120 | 2021-01-09 |
|       10 |        108 |       15 |           180 | 2021-01-10 |
+----------+------------+----------+---------------+------------+

We will use these tables to calculate margin in MySQL. Here is the margin formula.

profit margin = (quantity sold*selling price – quantity purchased*cost price)/quantity sold*selling price

Once we get total sales and total cost, you can modify the above formula as per your requirement to calculate gross margin, net margin, etc.

Also read : How to Get Top Selling Products using SQL

Calculate Total Margin in MySQL

Here is the SQL query to calculate total profit margin.

mysql> select (total_sales-total_cost)*100/total_sales as total_margin 
      from
      (select sum(quantity*cost_price) as total_cost 
         from purchases) as total_purchases,
      (select sum(quantity*selling_price) as total_sales 
         from sales) as total_sales;
 +--------------+
 | total_margin |
 +--------------+
 |      57.6059 |
 +--------------+

Let us look at the above query in detail.

We calculate total cost and total sales separately using subqueries

select sum(quantity*cost_price) as total_cost from purchases

and

select sum(quantity*selling_price) as total_sales from sales

and then use the result of these subqueries to calculate total profit margin.

Bonus read : How to Get Multiple Counts in MySQL

Calculate product margin in MySQL

Here is the SQL query to calculate profit margin for each product.

mysql> select total_purchases.product_id,
      (total_sales-total_cost)*100/total_sales as total_margin 
      from
      (select product_id,sum(quantity*cost_price) as total_cost          
          from purchases 
          group by product_id) as total_purchases,
      (select product_id,sum(quantity*selling_price) as total_sales 
          from sales 
          group by product_id) as total_sales 
          where total_purchases.product_id=total_sales.product_id;
 +------------+--------------+
 | product_id | total_margin |
 +------------+--------------+
 |        101 |      54.2889 |
 |        102 |      54.2105 |
 |        103 |      74.0741 |
 |        104 |      67.6190 |
 |        105 |      85.7143 |
 |        108 |      40.7407 |
 |        110 |      26.1111 |
 +------------+--------------+

In the above query, we use following two subqueries to separately calculate sales and cost for each product.

select product_id,sum(quantity*cost_price) as total_cost          
   from purchases group by product_id

and

select product_id,sum(quantity*selling_price) as total_sales 
   from sales group by product_id

Then we join the result of these two subqueries based on product_id column to calculate total profit margin for each product.

Also read : How to Use CASE statement in MySQL

Calculate daily margin in MySQL

Similarly, you can calculate daily profit margin using following SQL query.

mysql> select total_purchases.order_date,(total_sales-total_cost)*100/total_sales as total_margin 
     from
      (select date(order_date) as order_date,
        sum(quantity*cost_price) as total_cost 
        from purchases group by date(order_date)
      ) as total_purchases,   
      (select date(order_date) as order_date,
        sum(quantity*selling_price) as total_sales 
        from sales group by date(order_date)
      ) as total_sales 
    where total_purchases.order_date=total_sales.order_date;
 +------------+--------------+
 | order_date | total_margin |
 +------------+--------------+
 | 2021-01-01 |      46.4286 |
 | 2021-01-02 |      50.0000 |
 | 2021-01-03 |      65.7143 |
 | 2021-01-04 |      74.0741 |
 | 2021-01-05 |      85.7143 |
 | 2021-01-06 |      67.6190 |
 | 2021-01-07 |      -3.8462 |
 | 2021-01-08 |      55.0000 |
 | 2021-01-09 |      26.1111 |
 | 2021-01-10 |      40.7407 |
 +------------+--------------+

In the above query, we use following two subqueries to separately calculate sales and cost for each day.

select date(order_date) as order_date,sum(quantity*cost_price) as total_cost 
from purchases group by date(order_date)

and

select date(order_date) as order_date,sum(quantity*selling_price) as total_sales 
from sales group by date(order_date)

Then we join the result of these two subqueries based on order_date column to calculate total margin for each day.

Calculate Margin in 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 daily calculate margin in Ubiq.