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.

Next: SQL Tutorial Videos




Copyright © 2014 1keydata.com   All Rights Reserved.   Privacy Policy