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

NameSales
John10
Jennifer15
Stella20
Sophia40
Greg50
Jeff20

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:

NameSalesPct_To_Total
Greg500.3226
Sophia400.5806
Stella200.7097
Jeff200.8387
Jennifer150.9355
John101.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

OperationDescription
RankCalculates the ranking of a series of numbers
MedianCalculates the median of a series of numbers
Running TotalsCalculates the running total for a series of numbers
Percent To TotalCalculates the percent to total for each number in a series
Cumulative Percent To TotalCalculates the cumulative percent to total for each number in a series


Next: SQL Functions




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