It is important to calculate revenue for every business. Here’s how to calculate revenue in MySQL. You can use it to calculate daily revenue, month by month revenue, year by year revenue, revenue for last 12 months, for current month and more. We will look at each use case in detail.
How to Calculate Revenue in MySQL
Here are the steps to calculate revenue in MySQL.
Let’s say you have 2 tables products(product_id, product_name, price) and orders(order_date,product_id, quantity).
mysql> create table products(product_id int,product_name varchar(255),price int);
mysql> insert into products(product_id,product_name,price)
values(1,'iPhone 11',400),(2,'Samsung Galaxy A50',250);
mysql> select * from products;
+------------+--------------------+-------+
| product_id | product_name | price |
+------------+--------------------+-------+
| 1 | iPhone 11 | 400 |
| 2 | Samsung Galaxy A50 | 250 |
+------------+--------------------+-------+
mysql> create table orders(order_date date,product_id int,quantity int);
mysql> insert into orders(order_date,product_id,quantity)
values('2020-05-01',1,23),('2020-05-01',2,35),
('2020-05-02',1,45),('2020-05-02',2,23),('2020-05-03',1,19),
('2020-05-03',2,15),('2020-05-04',1,34),('2020-05-04',2,56);
mysql> select * from orders;
+------------+------------+----------+
| order_date | product_id | quantity |
+------------+------------+----------+
| 2020-05-01 | 1 | 23 |
| 2020-05-01 | 2 | 35 |
| 2020-05-02 | 1 | 45 |
| 2020-05-02 | 2 | 23 |
| 2020-05-03 | 1 | 19 |
| 2020-05-03 | 2 | 15 |
| 2020-05-04 | 1 | 34 |
| 2020-05-04 | 2 | 56 |
+------------+------------+----------+
For our example, revenue per order is basically price * quantity. We will look at different use cases to calculate revenue in MySQL
How to Calculate Daily Revenue in MySQL
Here’s the SQL query to calculate daily revenue in MySQL.
mysql> select date(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id group by date(order_date); +------------------+---------------------+ | date(order_date) | sum(price*quantity) | +------------------+---------------------+ | 2020-05-01 | 17950 | | 2020-05-02 | 23750 | | 2020-05-03 | 11350 | | 2020-05-04 | 27600 | +------------------+---------------------+
In the above SQL query, we multiply price x quantity to calculate revenue per order. Since price & quantity are in different tables, we join products and orders tables using the condition product.product_id=orders.product_id. Then we GROUP by DATE function to aggregate order revenue and get daily revenue.
After you calculate revenue in MySQL, you can use a reporting tool to plot this data on a bar chart or dashboard and share it with your team. Here’s an example of a bar chart that shows daily revenue, created using Ubiq.
Bonus Read : How to Create Histogram in MySQL
How to Calculate Month By Month Revenue in MySQL
Here’s the SQL query to calculate total revenue per month in MySQL. You just need to replace DATE function in above query with MONTH function
mysql> select month(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id group by month(order_date); +------------------+---------------------+ | month(order_date)| sum(price*quantity) | +------------------+---------------------+ | 1 | 127950 | | 2 | 223750 | | 3 | 311350 | | 4 | 427600 | +------------------+---------------------+
The above query will display month numbers with their revenue alongside. If you want to display month names, use DATE_FORMAT function
mysql> select date_format(order_date,'%b'),sum(price*quantity) from products,orders where products.product_id=orders.product_id group by date_format(order_date,'%b'); +------------------+---------------------+ | month(order_date)| sum(price*quantity) | +------------------+---------------------+ | Jan | 127950 | | Feb | 223750 | | Mar | 311350 | | Apr | 427600 | +------------------+---------------------+
How to Calculate Year By Year Revenue in MySQL
Here’s the SQL query to calculate annual revenue in MySQL. You just need to replace DATE function in above query with YEAR function
mysql> select year(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id group by year(order_date);
Bonus Read : How to Import CSV into MySQL Workbench
How to Calculate Revenue for Last 12 months in MySQL
Here’s the SQL query to calculate monthly revenue for last 12 months. You can use INTERVAL function to calculate revenue in MySQL, for last 12 months.
mysql> select month(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id and order_date > now() - INTERVAL 12 month group by month(order_date);
In the above query, we filter data that is after a past interval of 12 months.
How to Calculate Revenue for Last 3 months in MySQL
Here’s the SQL query to calculate monthly revenue for last 3 months. You can use INTERVAL function to calculate revenue in MySQL, for last 3 months.
mysql> select month(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id and order_date > now() - INTERVAL 3 month group by month(order_date);
In the above query, we filter data that is after a past interval of 3 months.
Bonus Read : How to Get Last 3 Months’ Sales Data in MySQL
How to Calculate Revenue for Current month in MySQL
Here’s the SQL query to calculate monthly revenue for current month. You can use INTERVAL function to calculate revenue in MySQL, for current month.
mysql> select month(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id and order_date >= (LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH) and order_date < (LAST_DAY(NOW()) + INTERVAL 1 DAY) group by month(order_date);
In the above query, we filter data that is after a past interval of current month.
Bonus Read : How to Get Records of Current Month in MySQL
How to Calculate Revenue for Current Quarter in MySQL
Here’s the SQL query to calculate monthly revenue for current month. You can use QUARTER function to calculate revenue in MySQL, for current quarter.
mysql> select month(order_date),sum(price*quantity) from products,orders where products.product_id=orders.product_id and QUARTER(order_date)=QUARTER(now()) and YEAR(order_date)=YEAR(now()) group by month(order_date);
In the above query, we filter data where quarter of order_date is same as quarter of present date.
Hopefully, the above queries can help you calculate revenue in MySQL. If you want to create charts, dashboards & reports from MySQL database, you can try Ubiq. We offer a 14-day free trial.
