SQL > Advanced SQL > Cumulative Percent To Total

There are multiple ways to show the cumulative percent to total in SQL. Here we will show two ways to do this: One without using the window function, and the second one using the window function.

Not using window function

To display cumulative percent to total in SQL, we use the same idea as we saw in the Percent To Total section. The difference is that we want the cumulative percent to total, not the percentage contribution of each individual row. Let's use the following example to illuatrate:

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, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name Sales Pct_To_Total
Greg 500.3226
Sophia 400.5806
Stella 200.7097
Jeff 200.8387
Jennifer 150.9355
John 101.0000

The subquery SELECT SUM(Sales) FROM Total_Sales calculates the sum. We can then divide the running total, "SUM(a2.Sales)", by this sum to obtain the cumulative percent to total for each row.

Using window function

The logic is mostly the same as when we are calculating the running total. Indeed, we need to first calculate the running total before we can calculate the cumulative percent to total.

The SQL is as follows:

SELECT a1.Name, a1.Sales, SUM(a1.Sales) OVER (ORDER BY Sales DESC) * 1.0 / a2.total RT
FROM Total_Sales a1, (SELECT SUM(sales) FROM Total_Sales) a2
ORDER BY Sales DESC;

Here we first calculate the overall total from the Total_Sales table and store it in a2. Next we divide the running total by this total. There is nothing to join between the two tables as a2 is simply a number.

Result:

Name Sales Pct_To_Total
Greg 500.3226
Sophia 400.5806
Stella 200.7097
Jeff 200.8387
Jennifer 150.9355
John 101.0000

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 be have the float data type, which is what we want.

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 Functions

This page was last updated on June 19, 2022.




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