Last updated on June 21st, 2024 at 09:05 am
It is important to calculate profit for every business. There are several financial metrics to get at a glance view of your business’ health quickly. Profit margins are one of the most important ones. Typically they can be calculated from your sales data itself, often stored in a database. 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.
Why Calculate Margin?
Margin gives a peek into your business’ financial health. It tells if your business is profitable, product is profitable, brand is profitable, depending on the entity for whom you calculate the margin. It also helps you set the selling prices of your products and also benchmark your financial performance against those of your competitors. There are different types of margins that you can calculate for your business. For our purpose, we will calculate total margin, product margin and daily margin. These are useful key performance indicators for every business.
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.
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.
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.
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.
In each of the 3 cases, we have used the same method. First calculate the sales and cost values using SQL queries and use them as subqueries to calculate margin. Only the granularity of aggregation of sales and cost changes in each case. It is handled by using GROUP BY clause in subqueries.
If your sales data is not in the format we have used you can transform your data to bring it to this format and then proceed as described above, or modify your margin calculation queries accordingly.
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.
In fact, after you run the query, you can simply click a visualization type to plot the result in a chart.
Conclusion
In this article, we have learnt how to calculate margin in MySQL. We have learnt how to calculate total margin, product margins and daily margins. Basically, we need to calculate total sales and total cost at different levels of granularity – total, product, and day. For product-wise and daily aggregations, we use GROUP BY clause to do the aggregation. You can customize these calculations as per your requirement. In fact, you can use the same method to calculate other financial metrics such as gross profit, operating profit and net profit also.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read
How to Get Top Selling Products using SQL
How to Get Multiple Counts in MySQL
How to Use CASE statement in MySQL
How to Use Coalesce in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.