Aggregation Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Aggregation

Analytics can be used with aggregation. When combining analytics with aggregation, all expressions in the ORDER BY clause and column expressions have to be either part of the GROUP BY expressions or aggregate functions.

SELECT
   ROW_NUMBER() OVER (ORDER BY DEPTNO) "ROW_NUMBER",
   DEPTNO,
   SUM(SAL),
   TO_CHAR
   (
      100*RATIO_TO_REPORT
      (
         SUM(SAL)
      )
      OVER
      (),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT
FROM
   EMP
GROUP BY
   DEPTNO;

ROW_NUMBER     DEPTNO   SUM(SAL)               PCT
---------- ---------- ---------- -----------------
         1         10       8750            30.15%
         2         20      10875            37.47%
         3         30       9400            32.39%


ROW_NUMBER is evaluated after the GROUP BY and ordered by the department number. RATIO_TO_REPORT returns the percentage of the total salary of the department to the overall total.

Used with ROLLUP, CUBE or GROUPING SETS, analytic functions can use GROUPING in the partition clause.

SELECT
   ENAME,
   DEPTNO,
   JOB,
   SUM(SAL),
   TO_CHAR
   (
      100*RATIO_TO_REPORT(SUM(SAL))
        OVER (PARTITION BY GROUPING(ENAME),GROUPING(DEPTNO),JOB),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT_JOB,  
   TO_CHAR
   (
      100*RATIO_TO_REPORT(SUM(SAL))
         OVER (PARTITION BY GROUPING(ENAME),GROUPING(JOB),DEPTNO),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT_DEPTNO,  
   TO_CHAR
   (
      100*RATIO_TO_REPORT(SUM(SAL))
         OVER (PARTITION BY GROUPING(ENAME),GROUPING(DEPTNO),GROUPING(JOB)),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT_EMP
FROM
   EMP
GROUP BY
   GROUPING SETS
   (
      (ENAME,DEPTNO,JOB),
      (DEPTNO,JOB),
      (DEPTNO),
      (JOB),
      ()
   )
ORDER BY
   GROUPING(ENAME),
   GROUPING(JOB),
   GROUPING(DEPTNO),
   DEPTNO,
   JOB,
   ENAME;

ENAME   DEPTNO JOB         SUM(SAL)      PCT_JOB   PCT_DEPTNO      PCT_EMP
------- ------ --------- ---------- ------------ ------------ ------------
MILLER      10 CLERK           1300       31.33%       14.86%        4.48%
CLARK       10 MANAGER         2450       29.61%       28.00%        8.44%
KING        10 PRESIDENT       5000      100.00%       57.14%       17.23%
FORD        20 ANALYST         3000       50.00%       27.59%       10.34%
SCOTT       20 ANALYST         3000       50.00%       27.59%       10.34%
ADAMS       20 CLERK           1100       26.51%       10.11%        3.79%
SMITH       20 CLERK            800       19.28%        7.36%        2.76%
JONES       20 MANAGER         2975       35.95%       27.36%       10.25%
JAMES       30 CLERK            950       22.89%       10.11%        3.27%
BLAKE       30 MANAGER         2850       34.44%       30.32%        9.82%
ALLEN       30 SALESMAN        1600       28.57%       17.02%        5.51%
MARTIN      30 SALESMAN        1250       22.32%       13.30%        4.31%
TURNER      30 SALESMAN        1500       26.79%       15.96%        5.17%
WARD        30 SALESMAN        1250       22.32%       13.30%        4.31%
            10 CLERK           1300       31.33%       14.86%        4.48%
            10 MANAGER         2450       29.61%       28.00%        8.44%
            10 PRESIDENT       5000      100.00%       57.14%       17.23%
            20 ANALYST         6000      100.00%       55.17%       20.67%
            20 CLERK           1900       45.78%       17.47%        6.55%
            20 MANAGER         2975       35.95%       27.36%       10.25%
            30 CLERK            950       22.89%       10.11%        3.27%
            30 MANAGER         2850       34.44%       30.32%        9.82%
            30 SALESMAN        5600      100.00%       59.57%       19.29%
               ANALYST         6000      100.00%       20.67%       20.67%
               CLERK           4150      100.00%       14.30%       14.30%
               MANAGER         8275      100.00%       28.51%       28.51%
               PRESIDENT       5000      100.00%       17.23%       17.23%
               SALESMAN        5600      100.00%       19.29%       19.29%
            10                 8750       30.15%      100.00%       30.15%
            20                10875       37.47%      100.00%       37.47%
            30                 9400       32.39%      100.00%       32.39%
                              29025      100.00%      100.00%      100.00%

The result set contains rows, subtotals and grand totals for each employee, department and job with the total salary.

For SCOTT, the department is 20, the job is CLERK and the salary is 3000; 3000 represents 50% of the sum of salaries of analysts where neither the name nor the department is a subtotal; 3000 is 27.59% of the sum of salaries of department 20 where neither the name nor the department is a subtotal; and 3000 is 10.34% of the sum of salaries in the employee table where neither the name, the job nor the department are subtotals.

For the subtotals for salesmen in department 30, the sum of salaries is 5600; 5600 is 100% of the sum of the totals of salaries of salesmen per department where the name is a subtotal but the department is not a subtotal (5600); 5600 is 59.57% of the sum of the totals in department 30 per job where name is a subtotal but job is not a subtotal (950 + 2850 + 5600); and 5600 is 19.29% of the total of all subtotals per job and department (1300 + 2450 + 5000 + 600 + 1900 + 2975 + 950 + 2850 + 5600).

For the subtotal of department 10, the sum of salaries is 8750; 8750 is 30.15% of the sum of the subtotals per department where the name and the job are subtotals but the department is not a subtotal (8750 + 10875 + 9400).