How to Calculate Percentage in MySQL
- Use One Column to Calculate Percentage in MySQL
- Use Two Columns to Calculate Percentage in MySQL
-
Use the
OVER()Function to Calculate Percentage in MySQL
We will calculate the percentage in MySQL using one or multiple columns. There are different ways to do it, and for every approach, we will be using a sample table.
Use One Column to Calculate Percentage in MySQL
We have a table named sales where ID, RepresentativeName, and Sale are column names. The following queries can be used to create and populate the sales table to practice with this tutorial.
Example Code:
# create a table
CREATE TABLE sales (
ID INT NOT NULL,
RepresentativeName VARCHAR(45) NOT NULL,
Sale INT NOT NULL,
PRIMARY KEY (ID));
# insert data
INSERT INTO sales (ID, RepresentativeName, Sale) VALUES
(1, 'John', 15),
(2, 'Mehvish', 15),
(3, 'Saira', 30);
# display sales table data
SELECT * FROM sales;
Output:
+----+--------------------+------+
| ID | RepresentativeName | Sale |
+----+--------------------+------+
| 1 | John | 15 |
| 2 | Mehvish | 15 |
| 3 | Saira | 30 |
+----+--------------------+------+
3 rows in set (0.00 sec)
To find the percentage of the Sale field, we can CROSS JOIN the SUM() function of the Sale attribute with the original relation (table). See the following query to do that.
Example Code:
SELECT RepresentativeName, Sale,
round(((Sale * 100) / temp.SalesSum),2) AS Percentage
FROM sales
CROSS JOIN (SELECT SUM(Sale) AS SalesSum FROM sales) temp;
Output:
+--------------------+------+------------+
| RepresentativeName | Sale | Percentage |
+--------------------+------+------------+
| John | 15 | 25.00 |
| Mehvish | 15 | 25.00 |
| Saira | 30 | 50.00 |
+--------------------+------+------------+
3 rows in set (0.00 sec)
Here, we use the round() method to get results for two decimal places. If we focus on the query used to find percentage, we can see that we are using the subquery after the CROSS JOIN keyword to find the sum of the Sale attribute.
Use Two Columns to Calculate Percentage in MySQL
We create a table named tests with an ID, GroupName, EmployeesCount, and SurveysCount as column names where ID is the PRIMARY KEY. Use the following queries to move with us throughout the tutorial.
Example Code:
# create a table
CREATE TABLE tests (
ID INT NOT NULL,
GroupName VARCHAR(45) NOT NULL,
EmployeesCount INT NOT NULL,
SurveysCount INT NOT NULL,
PRIMARY KEY (ID));
# insert data
INSERT INTO tests (ID, GroupName, EmployeesCount, SurveysCount) VALUES
(1, 'Group A', '200', '10'),
(2, 'Group B', '300', '200'),
(3, 'Group C', '400', '300');
# display tests table data
SELECT * FROM tests;
Output:
+----+-----------+----------------+---------------+
| ID | GroupName | EmployeesCount | SurveysCount |
+----+-----------+----------------+---------------+
| 1 | Group A | 200 | 10 |
| 2 | Group B | 300 | 200 |
| 3 | Group C | 400 | 300 |
+----+-----------+----------------+---------------+
3 rows in set (0.00 sec)
We use the following query to calculate the percentage using the EmployeesCount and SurveysCount fields.
Example Code:
SELECT GroupName, EmployeesCount, SurveysCount, COUNT( SurveysCount ) AS testA,
concat(round(( SurveysCount/EmployeesCount * 100 ),2),'%') AS Percentage
FROM tests
GROUP BY EmployeesCount;
Output:
+-----------+----------------+--------------+-------+------------+
| GroupName | EmployeesCount | SurveysCount | testA | Percentage |
+-----------+----------------+--------------+-------+------------+
| Group A | 200 | 10 | 1 | 5.00% |
| Group B | 300 | 200 | 1 | 66.67% |
| Group C | 400 | 300 | 1 | 75.00% |
+-----------+----------------+--------------+-------+------------+
3 rows in set (0.00 sec)
We calculate the percentage by dividing the SurveysCount by EmployeesCount and multiplying by 100. We use the round() function to round it for two decimal places to make it more readable.
Further, concatenate it with the % symbol using the concat() function to make it easy to understand.
Use the OVER() Function to Calculate Percentage in MySQL
The OVER() function is one of the Window Functions in MySQL that computes the values over a certain range of values. We can use this function to calculate percentages as well.
The OVER() function is very useful and helps us avoid the subqueries for computing percentages. Create a products table having ProductID, ProductName, and SupplierID as attribute names to understand this function.
Technically, the SupplierID must be a foreign key, but we are taking it as a simple field just for demonstration. Use the following queries to create the products table and insert data.
Example Code:
CREATE TABLE products (
ProductID INT NOT NULL,
ProductName VARCHAR(45) NOT NULL,
SupplierID INT NOT NULL,
PRIMARY KEY (ProductID));
INSERT INTO products (ProductID, ProductName, SupplierID)
VALUES
(1,'Coca Cola', 2),
(2, 'Wavy Chips', 2),
(3, 'Dairy Milk Chocolate', 1),
(4, 'Parley Biscuits', 3),
(5, 'Knorr Nodles', 3),
(6, 'Snickers Chocolate', 3);
SELECT * FROM products;
Output:
+-----------+----------------------+------------+
| ProductID | ProductName | SupplierID |
+-----------+----------------------+------------+
| 1 | Coca Cola | 2 |
| 2 | Wavy Chips | 2 |
| 3 | Dairy Milk Chocolate | 1 |
| 4 | Parley Biscuits | 3 |
| 5 | Knorr Nodles | 3 |
| 6 | Snickers Chocolate | 3 |
+-----------+----------------------+------------+
6 rows in set (0.00 sec)
Now, use the following query to calculate the percentage of products supplied by each supplier. We use the OVER() function instead of subqueries to get the products’ sum.
Example Code:
SELECT SupplierID AS Supplied_By, count(*) * 100.0 / sum(count(*)) Over() as 'Supplier Percentage'
FROM products
GROUP BY SupplierID;
Output:
+-------------+---------------------+
| Supplied_By | Supplier Percentage |
+-------------+---------------------+
| 2 | 33.33333 |
| 1 | 16.66667 |
| 3 | 50.00000 |
+-------------+---------------------+
3 rows in set (0.09 sec)
