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.

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 Cumulative Percent To Total




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