SQL > Advanced SQL > Percent To Total

To display percent to total in SQL, we want to leverage the ideas we used for rank/running total plus subquery. Different from what we saw in the SQL Subquery section, here we want to use the subquery as part of the SELECT. Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

NameSales
John10
Jennifer15
Stella20
Sophia40
Greg50
Jeff20

we would type,

SELECT a1.Name, a1.Sales, a1.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.2581
Stella200.1290
Jeff200.1290
Jennifer150.0968
John100.0645

The subquery SELECT SUM(Sales) FROM Total_Sales calculates the sum. We can then divide the individual values by this sum to obtain the percent to total for each row.

Next: SQL Cumulative Percent To Total




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