SQL Percent To Total

To calculate percent to total in SQL, we need to first calculate the total, and then we divide each individual value by the total to find the percentage. So this is a two-step process. There are multiple ways to accomplish this. Here we will show three different ways:

• Inline View in SELECT
• Inline View in FROM
• Using Common Table Expression (CTE)

Inline View in SELECT

The first method is to use the inline view construct in the SELECT statement. The idea here is to treat the total as a single number that we can directly use as the denominator in the division.

Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

we would type,

SELECT a1.Name, a1.Sales, a1.Sales * 1.0/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

The inline view 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.

We include * 1.0 because in some databases (such as SQLite), dividing an integer by another integer results in an integer, which is not what we want. Multiplying the numerator by 1.0 forces the expression to be considered as a float by the database, and the result of the division will have the float data type, which is what we want.

Inline View in FROM

The second method is to the inline view in the FROM statement. Here, the inline view essentially becomes another table that you can query from. Note that here we do not need to specify the join condition between the two tables, as the inline view only has a single column and a single row. In this case, the SQL would become as follows:

SELECT a1.Name, a1.Sales, a1.Sales * 1.0 / a2.Total Pct_To_Total
FROM Total_Sales a1, (SELECT SUM(Sales) Total FROM Total_Sales) a2
ORDER BY a1.Sales DESC, a1.Name DESC;

Using Common Table Expression (CTE)

A third way to calculate percent to total is to use the Common Table Expression (CTE). In this case, we will first use the WITH statement to calculate the total, and then use the result in the main query to calculate the percent to total. In our example, the SQL would look like the following:

WITH Total_Sum AS (
SELECT SUM(Sales) Total FROM Total_Sales
)

SELECT a1.Name, a1.Sales, a1.Sales * 1.0 / a2.Total Pct_To_Total
FROM Total_Sales a1, Total_Sum a2
ORDER BY a1.Sales DESC, a1.Name DESC;

List of SQL Complex Operations