How to Calculate Percentage of Two Columns in MySQL

Last updated on May 17th, 2020 at 06:28 am

Sometimes you may need to simply calculate a percentage of two columns in MySQL databases. Here’s how to easily calculate percentage of two columns in MySQL.

How to Calculate Percentage of Two Columns in MySQL

Here are the SQL to calculate percentage of two columns in MySQL. Let’s say you have the following table – sales(sales_rep,sale,goal)

mysql> create table sales(sales_rep varchar(255),sale int, goal int);

mysql> insert into sales(sales_rep,sale,goal) 
values('Albert',10,20),('Bob',23,33),('Chris',20,25),('Dave',35,40);

mysql> select * from sales;
+-----------+------+------+
| sales_rep | sale | goal |
+-----------+------+------+
| Albert    |   10 |   20 |
| Bob       |   23 |   33 |
| Chris     |   20 |   25 |
| Dave      |   35 |   40 |
+-----------+------+------+

Let’s say you want to calculate attainment_percent as sale/goal*100 for each sales rep.

Here’s the SQL query to calculate percentage of two columns (sale, goal). You can directly calculate it on the fly using division and multiplication operators, as shown below.

mysql> select sales_rep,sale,goal,
(sale/goal)*100 as attainment_percent from sales;
+-----------+------+------+--------------------+
| sales_rep | sale | goal | attainment_percent |
+-----------+------+------+--------------------+
| Albert    |   10 |   20 |            50.0000 |
| Bob       |   23 |   33 |            69.6970 |
| Chris     |   20 |   25 |            80.0000 |
| Dave      |   35 |   40 |            87.5000 |
+-----------+------+------+--------------------+

Bonus Read : How to Automate Pivot Table Queries in MySQL

If you want to round the percent numbers to 2 decimal places, you can use the ROUND function as shown

mysql> select sales_rep,sale,goal,
round((sale/goal)*100,2) as attainment_percent from sales;
+-----------+------+------+--------------------+
| sales_rep | sale | goal | attainment_percent |
+-----------+------+------+--------------------+
| Albert    |   10 |   20 |              50.00 |
| Bob       |   23 |   33 |              69.70 |
| Chris     |   20 |   25 |              80.00 |
| Dave      |   35 |   40 |              87.50 |
+-----------+------+------+--------------------+

If you want to calculate percentage of two columns only for specific rows, add WHERE clause as shown below in bold

mysql> select sales_rep,sale,goal
,round((sale/goal)*100,2) as attainment_percent
 from sales
 where sales_rep in ('Bob','Dave');
+-----------+------+------+--------------------+
| sales_rep | sale | goal | attainment_percent |
+-----------+------+------+--------------------+
| Bob       |   23 |   33 |              69.70 |
| Dave      |   35 |   40 |              87.50 |
+-----------+------+------+--------------------+

Bonus Read : How to Calculate Moving Average in MySQL

If your total data is spread across multiple columns, as shown below,

mysql> create table sales(sales_rep varchar(255),sale int, sale2 int,sale3 int);

mysql> insert into sales(sales_rep,sale,sale2,sale3) 
values('Albert',10,20,10),('Bob',23,33,20),
('Chris',20,25,15),('Dave',35,40,20);

mysql> select * from sales;
+-----------+------+-------+-------+
| sales_rep | sale | sale2 | sale3 |
+-----------+------+-------+-------+
| Albert    |   10 |    20 |    10 |
| Bob       |   23 |    33 |    20 |
| Chris     |   20 |    25 |    15 |
| Dave      |   35 |    40 |    20 |
+-----------+------+-------+-------+

then you can calculate percent of each column by totaling their values on the fly, as shown below.

mysql> select sales_rep,sale, sale2,sale3, 
(sale/(sale+sale2+sale3))*100 as sale_pct, 
(sale2/(sale+sale2+sale3))*100 as sale2_pct, 
(sale3/(sale+sale2+sale3))*100 as sale3_pct 
from sales;
+-----------+------+-------+-------+----------+-----------+-----------+
| sales_rep | sale | sale2 | sale3 | sale_pct | sale2_pct | sale3_pct |
+-----------+------+-------+-------+----------+-----------+-----------+
| Albert    |   10 |    20 |    10 |  25.0000 |   50.0000 |   25.0000 |
| Bob       |   23 |    33 |    20 |  30.2632 |   43.4211 |   26.3158 |
| Chris     |   20 |    25 |    15 |  33.3333 |   41.6667 |   25.0000 |
| Dave      |   35 |    40 |    20 |  36.8421 |   42.1053 |   21.0526 |
+-----------+------+-------+-------+----------+-----------+-----------+

You can customize the above query to calculate percentage of two columns, as per your requirement.

You can also use a reporting tool to plot this data on a table or dashboard and share it with your team. Here’s an example of a table with above data, created using Ubiq.