SQL > Advanced SQL > Cumulative Percent To Total

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.

### 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