JIYIK CN >

We will use one or more columns to calculate percentages in MySQL. There are different ways to do this, and for each method we will use an example table.

Calculate percentage using a column in MySQL

We have a table called sales where ID, RepresentativeName, and Sale are the column names. The following query can be used to create and populate the sales table to practice this tutorial.

Sample 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 Sale field, we can CROSS JOIN the SUM() function of Sale attribute with the original relation (table). See the following query to do this.

Sample 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 round()the method to get the result to two decimal places. If we focus on the query used to find the percentage, we can see that we are using a subquery after the CROSS JOIN keyword to find the sum of the Sale attribute.

Calculating percentages using two columns in MySQL

We create a table named tests with ID, GroupName, EmployeesCount and SurveysCount as column names where ID is the PRIMARY KEY. Use the following query to move with us throughout the tutorial.

Sample 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.

Sample 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 SurveysCount by EmployeesCount and multiplying by 100. We use the round() function to round it to two decimal places to make it more readable.

Also, using concat()the function to concatenate it with the % symbol makes it easy to understand.

Calculating Percentages in MySQL Using the OVER() Function

OVER()The function is one of the window functions in MySQL and is used to calculate values ​​within a specific range of values. We can also use this function to calculate percentages.

OVER()Functions are very useful and can help us avoid subqueries to calculate percentages. Create a Products table with ProductID, ProductName, and SupplierID as attribute names to see this functionality.

Technically, SupplierID must be a foreign key, but we'll leave it as a simple field for demonstration purposes. Use the following queries to create the Products table and insert data.

Sample 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 provided by each supplier. We use OVER()the function instead of a subquery to get the sum of the products.

Sample 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)