Calculating Percentage (%) of Total Sum in SQL
How to compute the ratio of a particular row to the sum of values?
This question comes up frequently when you want to the relative contribution of a row against the backdrop of the total sum of all the rows. For example:
- how is the browser marketshare changing over time
- what's each sales person's contribution to your company's revenue
Consider a table with the number of page view (in billions) with each browser:
| Browser | Pageviews |
|---|---|
| Chrome | 7.1685 |
| Safari | 1.935 |
| Firefox | 1.3455 |
| UC Browser | 1.0965 |
| IE | 1.341 |
| Opera | 0.816 |
| Android | 0.7245 |
| Rest | 1.2 |
What we really want to see is the browser market share. We can use a Common Table Expression (CTE) to compute the total pageview which we then use to compute the ratio:
Which gives a ratio of each browser to the total:
| Browser | Share |
|---|---|
| Chrome | 0.895 |
| Safari | 0.241875 |
| Firefox | 0.1681875 |
| UC Browser | 0.1370625 |
| IE | 0.167625 |
| Opera | 0.102 |
| Android | 0.0905625 |
| Rest | 0.15 |
And the visualization:
Percentage to Total per Group
The next question to ask is how this is changing over time?
What we are attempting to do here is to group our data into months, compute the total for that group and for each row within that group compute a ratio. An overall total wouldn't make sense. Conside the pageview table as before, but with an additional date field:
| dt | Browser | Pageviews |
|---|---|---|
| 2016-01-01 | Chrome | 7.1685 |
| 2016-01-01 | Safari | 1.935 |
| 2016-01-01 | . | . |
| 2016-01-02 | Chrome | 7.2485 |
| 2016-01-02 | Safari | 1.721 |
| 2016-01-02 | . | . |
| . | . | . |
| 2016-12-31 | Chrome | 7.864 |
| 2016-12-31 | Safari | 2.011 |
| 2016-12-31 | . | . |
We once again to resort to window functions with a partition over the month portion of the datetime.
Let's unpack the query a bit. Our window function creates partitions (groups) for each month, and for each partition we sum up the pageviews. The ratio between the current row pageviews and the total for that group is exactly what we want.
Redshift has ratio_to_report
Fortunately on Redshift, it supports a window function called ratio_to_report which computes the ratio of the value of a column in a row with that of the sum of the said expression over the group.
How to calculate percentage with a SQL statement
I have a SQL Server table that contains users & their grades. For simplicity’s sake, lets just say there are 2 columns — name & grade . So a typical row would be Name: «John Doe», Grade:»A».
I’m looking for one SQL statement that will find the percentages of all possible answers. (A, B, C, etc. ) Also, is there a way to do this without defining all possible answers (open text field — users could enter ‘pass/fail’, ‘none’, etc. )
SQL percentage calculation examples in SQL Server
In this article, you will see the different ways to calculate SQL percentage between multiple columns and rows. You will also see how to calculate SQL percentages for numeric columns, grouped by categorical columns. You will use subqueries, the OVER clause, and the common table expressions (CTE) to find SQL percentages.
So, let’s begin without any ado.
Finding Percentage using Two Variables
There is no built-in operator that calculates percentages in SQL Server. You have to rely on basic arithmetic operations i.e. (number1/number2 x 100) to find percentages in SQL Server.
Before finding the SQL percentages across rows and columns, let’s first see how you can find percentages using two basic variables in SQL Server.
The script below defines three float variables @num1, @num2 and @perc. Next, the @num2 variable is divided by the @num1 variable and the result is multiplied by 100 which is stored in the @perc variable and is printed on the console.
Output:
Finding Percentages Between Two Columns
Finding Percentages between two columns is straightforward. You can simply use the column names and the division operator “/” to divide values in one column by another. The result is a list of values that correspond to the result of the division of all the values in the two columns.
Let’s see an example.
The script below, creates a table Result with two float type columns “obtained”, and “total”. The script also inserts five dummy rows in the Result table. The SELECT query then selects all the records in the Result table. Here is an example:
Output:
Let’s try to find percentages for each row as a result of the division between the values in the “obtained” and “total” columns as shown below where a new column is added for percentages.
Finding Percentages via Subqueries
Finding SQL percentages between two columns is straightforward. However, the process is not as straightforward for finding percentages across rows for different scenarios.
Let’s first discuss a very simple scenario where you have to find what is the percentage of a value in a column among all the rows in the column.
The following script creates a table Scores with one column.
Output:
Now if you want to find what percent of the sum of total values in the “val” column does each value constitutes, you can use subqueries.
In this regard, the outer query will multiply all the values in the “val” column by 100 which will be divided by the result of the subquery which finds the sum of all the values in the “val” column.
Let’s first see how our subquery looks that calculate the sum of the values in the “val” column.
Output:
The following script returns the percentage of the total for each value in the “val ” column.
If you do not want to exclude any value while calculating the percentage of the total, you can do so with the WHERE clause as shown in the script below where the value 40 is not included.
You can see from the above output that the values now have a larger percentage share of total values since the value 40 is removed.
Finally, as a side note, you can round off the percentages returned using the “round” function as shown below. The script below rounds off the percentage values to 2 decimal places.
Output:
Let’s now see a real-world example of how you can calculate SQL percentage. You will be using the Northwind sample database which you can download and install from this link.
Run the following script to see the columns in the Products table.
Output:
The Products table contains columns that contain Supplier ID, Category ID, Unit Price and other information about the products in the Northwind database.
Consider a scenario where you have to find the percentage of the products supplied by each supplier. To find such percentage values, you need two values:
- The total number of all the products which you can get via the COUNT function
- The total number of products supplied for each supplier, which you can get using the GROUP BY function.
You can then multiply the 2 nd value (count of products grouped by supplier ids) by 100 and then divide the result by the 1 st value (total count of products).
Here is how you can use subqueries to find these such SQL percentages.
Output:
The above results show that the percentage of products supplied by each supplier. For instance, you can see that the supplier with id 1 supplied 3.89% of the total products.
USING Over Clause
The Over clause is an extremely useful Window function that calculates values over a range of values. You can use the Over clause to calculate SQL percentages as well. With the Over clause, you can avoid the use of subqueries for calculating percentages.
Let’s see an example. The script below finds the percentage of products supplied by every supplier. You can see that the following script is very similar to what you saw in the previous section. However, in this case, instead of using a subquery that returns the count of all products, we use the Over clause which returns the sum of all the products.
The output below is similar to what you achieved using a subquery.
Output:
Let’s now see a more complex example of finding SQL percentages. Consider a scenario where for each supplier you want to calculate the percentage of unit price for all the products. In other words, you want to find out that what percentage of the sum of unit prices for all the products is being paid to a different supplier. You can do so with the help of the OVER clause as follows.
In the script below, the supplier ids are displayed along with the sum of unit prices paid to all suppliers, and the percentages of unit prices paid to all suppliers.
To calculate the sum of unit prices, the formula is simple, you can use the SUM function and pass it to the column containing the unit prices.
To calculate the percentages for unit prices, you need to multiply the sum of unit prices for each supplier by 100 and then divide the result with the total sum of unit prices for all the suppliers. In the script below, in the denominator, the SUM function is called twice. Since we are using the OVER operator, the first SUM function only adds the prices for each supplier. To find the sum of unit prices paid to all suppliers, another SUM function is called.
Функции подсчета долей
Функции подсчета долей позволяют одной SQL-операцией получить для каждой строки ее «вес» в таблице в соответствии с ее значениями. Некоторые примеры:
SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS salshare FROM emp;
(доли сотрудников в общей сумме зарплат)
Пример выдачи доли сотрудников с меньшей или равной зарплатой, чем у «текущего»:
SELECT job, ename, sal, CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist FROM emp;
14 rows selected.
(видно, что три четверти клерков имеют зарплату, меньше чем ADAMS).
Проранжировать эту выдачу по доле сотрудников в группе можно функцией PERCENT_RANK:
SELECT job, ename, sal, CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist, PERCENT_RANK() OVER (PARTITION BY job ORDER BY sal) AS pct_rank FROM emp;
14 rows selected.
Процентный ранг отсчитывается от 0 и изменяется до 1.
Некоторые жизненные примеры аналитических запросов Для типов сегментов, более других расходующих дисковое пространство, выдать главных пользователей, ответственных за такой расход
Построить такой запрос на основе таблицы SYS.DBA_SEGMENTS, можно пошагово.
Шаг 1. Выдать типы сегментов в БД, общий объем памяти на диске для каждого типа и долю числа типов с равным или меньшим общим объемом памяти:
SELECT segment_type, SUM(bytes) bytes, CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile FROM sys.dba_segments GROUP BY segment_type;
Шаг 2.Отобрать 40% «наиболее расточительных» по дисковой памяти типов:
SELECT * FROM (SELECT segment_type, SUM(bytes) bytes, CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile FROM sys.dba_segments GROUP BY segment_type) WHERE bytes_percentile >= 0.5;
Шаг 3.Отобрать пользователей, занимающих первые пять мест по расходованию памяти среди «наиболее расточительных» типов сегментов: