How to Calculate Percentage of Column in MySQL

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

Sometimes you may need to calculate percentage of a column (e.g sales, revenue) in your data to understand what percent of your column’s total value is coming from each row (e.g country, state, etc). Since there is no out-of-the-box function to calculate this value, you need to write a SQL query for it. Here’s how to calculate percentage of column in MySQL

Table of Contents

How to Calculate Percentage of Column in MySQL

Let’s say you have the following table as shown below.

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

mysql> insert into sales values('Bob',15),('Sally',30),('Peter',15);

mysql> select * from sales;
+-------+------+
|  rep  | sale |
+-------+------+
|  Bob  |  15  |
| Sally |  30  |
| Peter |  15  |
+-------+------+

Let’s say you want to display a column ‘percent of total’ which is simply the percent of total sale column, as shown below.

+-------+------+------------------+
|  rep  | sale | percent of total |
+-------+------+------------------+
|  Bob  |  15  |        25        |
| Sally |  30  |        50        |
| Peter |  15  |        25        |
+-------+------+------------------+

You can do this in multiple ways using just a single query. There’s no need to write separate queries for calculating total and percentage. Let’s look at each of them

Bonus Read : How to Calculate Percentage of Two Columns in MySQL

How to Calculate Percentage of Column in MySQL using CROSS JOIN

To calculate percentage of column in MySQL, you can simply cross join the sum() of sale column with the original table.

SELECT Rep, Sale, Sale * 100 / t.s AS `percent of total`
FROM sales
CROSS JOIN (SELECT SUM(sale) AS s FROM sales) t;

+-------+------+------------------+
|  rep  | sale | percent of total |
+-------+------+------------------+
|  Bob  |  15  |        25        |
| Sally |  30  |        50        |
| Peter |  15  |        25        |
+-------+------+------------------+

If you want to add a where clause to filter your data, you need to place it after the CROSS JOIN, as shown below. Otherwise, you will get an error.

SELECT Rep, Sale, Sale * 100 / t.s AS `percent of total`
FROM sales
CROSS JOIN (SELECT SUM(sale) AS s FROM sales) t where Rep='Bob';
+------+------+------------------+
| Rep  | Sale | percent of total |
+------+------+------------------+
| Bob  |  15  |       25         |
+------+------+------------------+

However, if you want to add where clause during totaling of column, you need to add it in the select clause of total, as shown below. Let’s say, you want to exclude Bob from the total itself, then here’s a query to do it.

SELECT Rep, Sale, Sale * 100 / t.s AS `percent of total` 
FROM sales 
CROSS JOIN (SELECT SUM(sale) AS s FROM sales where Rep<>'Bob') t 
where Rep<>'Bob';
+-------+------+------------------+
|  Rep  | Sale | percent of total |
+-------+------+------------------+
| Sally |  30  |      66.6667     |
| Peter |  15  |      33.3333     |
+-------+------+------------------+

Bonus Read : How to Calculate Percentage Growth Week Over Week in MySQL

How to Calculate Percentage of Column in MySQL using SUBSELECT/SUBQUERY

You can also calculate percentage of column using a subselect, instead of using a JOIN, as shown below.

SELECT Rep, Sale, 
Sale * 100 / (SELECT SUM(sale) AS s FROM sales) AS `percent of total`
FROM sales;

+-------+------+------------------+
|  rep  | sale | percent of total |
+-------+------+------------------+
|  Bob  |  15  |        25        |
| Sally |  30  |        50        |
| Peter |  15  |        25        |
+-------+------+------------------+

If you want to add a where clause to filter your data, you need to place it after the CROSS JOIN, as shown below. Otherwise, you will get an error.

SELECT Rep, Sale, 
Sale * 100 / (SELECT SUM(sale) AS s FROM sales) AS `percent of total` 
FROM sales
where Rep='Bob';
+--------+--------+-------------------+
|   Rep  |  Sale  |  percent of total |
+--------+--------+-------------------+
|  Bob   |    15  |         25        |
+--------+--------+-------------------+

However, if you want to add where clause during totaling of column, you need to add it in the select clause of total. Let’s say, you want to exclude Bob from the total itself, then here’s a query to do it.

SELECT Rep, Sale, 
Sale * 100 / (SELECT SUM(sale) AS s FROM sales where Rep='Bob') AS `percent of total` 
FROM sales
where Rep='Bob';
+-------+------+------------------+
|  Rep  | Sale | percent of total |
+-------+------+------------------+
| Sally |  30  |      66.6667     |
| Peter |  15  |      33.3333     |
+-------+------+------------------+

You can customize the above queries as per your requirement to calculate percentage of column in MySQL.

Further, you can use a reporting tool to plot the data in a pie chart or dashboard. Here’s an example of a pie chart created using Ubiq.