SQL Percent To Total



  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 © 2013 1keydata.com   All Rights Reserved.     Privacy Policy


SQL UNION
SQL UNION ALL
SQL Inline View
SQL INTERSECT
SQL MINUS
SQL LIMIT
SQL TOP
SQL Subquery
SQL EXISTS
SQL CASE
SQL DECODE
SQL AUTO INCREMENT
SQL IDENTITY
SQL SEQUENCE And NEXTVAL
SQL NULL
SQL ISNULL
SQL IFNULL
SQL NVL
SQL COALESCE
SQL NULLIF
SQL Rank
SQL Median
SQL Running Totals
SQL Percent to Total
SQL Cumulative Percent to Total


SQL Video Tutorial
SQL Jobs




Site Map
Resources