SQL > Advanced SQL > Percent To Total

To calculate percent to total in SQL, we need to first calculate the total, and then we divide each individual value by the total to find the percentage. So this is a two-step process. There are multiple ways to accomplish this. Here we will show three different ways:

Inline View in SELECT

The first method is to use the inline view construct in the SELECT statement. The idea here is to treat the total as a single number that we can directly use as the denominator in the division.

Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

 Name  Sales 
 John  10 
 Jennifer  15 
 Stella  20 
 Sophia  40 
 Greg  50 
 Jeff  20 

we would type,

SELECT a1.Name, a1.Sales, a1.Sales * 1.0/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name Sales Pct_To_Total
Greg 500.3226
Sophia 400.2581
Stella 200.1290
Jeff 200.1290
Jennifer 150.0968
John 100.0645

The inline view SELECT SUM(Sales) FROM Total_Sales calculates the sum. We can then divide the individual values by this sum to obtain the percent to total for each row.

We include * 1.0 because in some databases (such as SQLite), dividing an integer by another integer results in an integer, which is not what we want. Multiplying the numerator by 1.0 forces the expression to be considered as a float by the database, and the result of the division will have the float data type, which is what we want.

Inline View in FROM

The second method is to the inline view in the FROM statement. Here, the inline view essentially becomes another table that you can query from. Note that here we do not need to specify the join condition between the two tables, as the inline view only has a single column and a single row. In this case, the SQL would become as follows:

SELECT a1.Name, a1.Sales, a1.Sales * 1.0 / a2.Total Pct_To_Total
FROM Total_Sales a1, (SELECT SUM(Sales) Total FROM Total_Sales) a2
ORDER BY a1.Sales DESC, a1.Name DESC;

Using Common Table Expression (CTE)

A third way to calculate percent to total is to use the Common Table Expression (CTE). In this case, we will first use the WITH statement to calculate the total, and then use the result in the main query to calculate the percent to total. In our example, the SQL would look like the following:

WITH Total_Sum AS (
SELECT SUM(Sales) Total FROM Total_Sales
)

SELECT a1.Name, a1.Sales, a1.Sales * 1.0 / a2.Total Pct_To_Total
FROM Total_Sales a1, Total_Sum a2
ORDER BY a1.Sales DESC, a1.Name DESC;

List of SQL Complex Operations

 Operation Description
 Rank  Calculates the ranking of a series of numbers. 
 Median  Calculates the median of a series of numbers. 
 Running Totals  Calculates the running total for a series of numbers. 
 Percent To Total  Calculates the percent to total for each number in a series. 
 Cumulative Percent To Total  Calculates the cumulative percent to total for each number in a series. 


Next: SQL Cumulative Percent To Total

This page was last updated on October 12, 2024.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact