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 50 0.3226 Sophia 40 0.5806 Stella 20 0.7097 Jeff 20 0.8387 Jennifer 15 0.9355 John 10 1.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 50 0.3226 Sophia 40 0.5806 Stella 20 0.7097 Jeff 20 0.8387 Jennifer 15 0.9355 John 10 1.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