Recipe7.11.Determining the Percentage of a Total

Recipe 7.11. Determining the Percentage of a Total

Problem

You want to determine the percentage that values in a specific column represent against a total. For example, you want to determine what percentage of all salaries are the salaries in DEPTNO 10 (the percentage that DEPTNO 10 salaries contribute to the total).

Solution

In general, computing a percentage against a total in SQL is no different than doing so on paper; simply divide, then multiply. In this example you want to find the percentage of total salaries in table EMP that come from DEPTNO 10. To do that, simply find the salaries for DEPTNO 10, and then divide by the total salary for the table. As the last step, multiply by 100 to return a value that represents a percent.

MySQL and PostgreSQL

Divide the sum of the salaries in DEPTNO 10 by the sum of all salaries:

 1 select (sum( 2          case when deptno = 10 then sal end)/sum(sal) 3         )*100 as pct 4   from emp 

DB2, Oracle, and SQL Server

Use an inline view with the window function SUM OVER to find the sum of all salaries along with the sum of all salaries in DEPTNO 10. Then do the division and multiplication in the outer query:

 1 select distinct (d10/total)*100 as pct 2   from ( 3 select deptno, 4        sum(sal)over( ) total, 5        sum(sal)over(partition by deptno) d10 6   from emp 7        ) x 8  where deptno=10 

Discussion

MySQL and PostgreSQL

The CASE statement conveniently returns only the salaries from DEPTNO 10. They are then summed and divided by the sum of all the salaries. Because NULLs are ignored by aggregates, an ELSE clause is not needed in the CASE statement. To see exactly which values are divided, execute the query without the division:

  select sum(case when deptno = 10 then sal end) as d10,        sum(sal)   from emp D10  SUM(SAL) ---- --------- 8750     29025 

Depending on how you define SAL, you may need to include explicit casts when performing division. For example, on DB2, SQL Server, and PostgreSQL, if SAL is stored as an integer, you can cast to decimal to get the correct answer, as seen below:

 select (cast(          sum(case when deptno = 10 then sal end)             as decimal)/sum(sal)         )*100 as pct   from emp 

DB2, Oracle, and SQL Server

As an alternative to the traditional solution, this solution uses window functions to compute a percentage relative to the total. For DB2 and SQL Server, if you've stored SAL as an integer, you'll need to cast before dividing:

 select distinct        cast(d10 as decimal)/total*100 as pct   from ( select deptno,        sum(sal)over() total,        sum(sal)over(partition by deptno) d10   from emp        ) x  where deptno=10 

It is important to keep in mind that window functions are applied after the WHERE clause is evaluated. Thus, the filter on DEPTNO cannot be performed in inline view X. Consider the results of inline view X without and with the filter on DEPTNO. First without:

  select deptno,        sum(sal)over() total,        sum(sal)over(partition by deptno) d10   from emp DEPTNO      TOTAL       D10 ------- --------- ---------      10     29025      8750      10     29025      8750      10     29025      8750      20     29025     10875      20     29025     10875      20     29025     10875      20     29025     10875      20     29025     10875      30     29025      9400      30     29025      9400      30     29025      9400      30     29025      9400      30     29025      9400      30     29025      9400 

and now with:

  select deptno,        sum(sal)over( ) total,        sum(sal)over(partition by deptno) d10   from emp  where deptno=10 DEPTNO     TOTAL       D10 ------ --------- ---------     10      8750      8750     10      8750      8750     10      8750      8750 

Because window functions are applied after the WHERE clause, the value for TOTAL represents the sum of all salaries in DEPTNO 10 only. But to solve the problem you want the TOTAL to represent the sum of all salaries, period. That's why the filter on DEPTNO must happen outside of inline view X.