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

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:

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:

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