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

 Name Sales John 10 Jennifer 15 Stella 20 Sophia 40 Greg 50 Jeff 20

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:

 Name Sales Pct_To_Total Greg 50 0.3226 Sophia 40 0.2581 Stella 20 0.1290 Jeff 20 0.1290 Jennifer 15 0.0968 John 10 0.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

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