14.4 Reporting Functions

Similar to the windowing functions described earlier, reporting functions allow the execution of various aggregate functions (MIN, MAX, SUM, COUNT, AVG, etc.) against a result set. Unlike windowing functions, however, the reporting functions cannot specify localized windows and thus generate the same result for each entire partition (or the entire result set, if no partitions are specified). Therefore, anything that can be accomplished using a reporting function can also be accomplished using a windowing function with an unbounded window, although it will generally be more efficient to use the reporting function.

Earlier in the chapter, we used a windowing function with an unbounded reporting window to generate the total sales for the 12 months of 2001:

SELECT month, 

  SUM(tot_sales) monthly_sales,

  SUM(SUM(tot_sales)) OVER (ORDER BY month 

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) total_sales

FROM orders

WHERE year = 2001 

  AND region_id = 6

GROUP BY month

ORDER BY month;



     MONTH MONTHLY_SALES TOTAL_SALES

---------- ------------- -----------

         1        610697     6307766

         2        428676     6307766

         3        637031     6307766

         4        541146     6307766

         5        592935     6307766

         6        501485     6307766

         7        606914     6307766

         8        460520     6307766

         9        392898     6307766

        10        510117     6307766

        11        532889     6307766

        12        492458     6307766

The next query adds a reporting function to generate the same results:

SELECT month, 

  SUM(tot_sales) monthly_sales,

  SUM(SUM(tot_sales)) OVER (ORDER BY month 

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) window_sales,

  SUM(SUM(tot_sales)) OVER ( ) reporting_sales

FROM orders

WHERE year = 2001 

  AND region_id = 6

GROUP BY month

ORDER BY month;



     MONTH MONTHLY_SALES WINDOW_SALES REPORTING_SALES

---------- ------------- ------------ ---------------

         1        610697      6307766         6307766

         2        428676      6307766         6307766

         3        637031      6307766         6307766

         4        541146      6307766         6307766

         5        592935      6307766         6307766

         6        501485      6307766         6307766

         7        606914      6307766         6307766

         8        460520      6307766         6307766

         9        392898      6307766         6307766

        10        510117      6307766         6307766

        11        532889      6307766         6307766

        12        492458      6307766         6307766

The empty parentheses after the OVER clause for the reporting_sales column indicate that the entire result set should be included in the sum, which has the same effect as using an unbounded window function. Hopefully, you will agree that the reporting function is easier to understand than the unbounded window function.

Reporting functions are useful when you need both detail and aggregate data (or different aggregation levels) to answer a business query. For example, the query "Show the monthly sales totals for 2001 along with each month's percentage of yearly sales" requires the detail rows to be aggregated first to the month level, and then to the year level to answer the question. Rather than computing both aggregations from the detail rows, you can use the SUM function with a GROUP BY clause to aggregate to the month level, and then use a reporting function to aggregate the monthly totals, as in:

SELECT month, 

  SUM(tot_sales) monthly_sales,

  SUM(SUM(tot_sales)) OVER ( ) yearly_sales

FROM orders

WHERE year = 2001

GROUP BY month

ORDER BY month;



     MONTH MONTHLY_SALES YEARLY_SALES

---------- ------------- ------------

         1       3028325     39594908

         2       3289336     39594908

         3       3411024     39594908

         4       3436482     39594908

         5       3749264     39594908

         6       3204730     39594908

         7       3233532     39594908

         8       3081290     39594908

         9       3388292     39594908

        10       3279637     39594908

        11       3167858     39594908

        12       3325138     39594908

You would then simply divide MONTHLY_SALES by YEARLY_SALES to compute the requested percentage (see Section 14.4.2 later in the chapter).

14.4.1 Report Partitions

Like ranking functions, reporting functions can include PARTITION BY clauses to split the result set into multiple pieces, allowing multiple aggregations to be computed across different subsets of the result set. The following query generates total sales per salesperson per region along with the total regional sales for comparison:

SELECT region_id, salesperson_id, 

  SUM(tot_sales) sp_sales,

  SUM(SUM(tot_sales)) OVER (PARTITION BY region_id) region_sales

FROM orders

WHERE year = 2001

GROUP BY region_id, salesperson_id

ORDER BY region_id, salesperson_id;



REGION_ID  SALESPERSON_ID   SP_SALES REGION_SALES

---------- -------------- ---------- ------------

         5              1    1927580      6585641

         5              2    1461898      6585641

         5              3    1501039      6585641

         5              4    1695124      6585641

         6              5    1688252      6307766

         6              6    1392648      6307766

         6              7    1458053      6307766

         6              8    1768813      6307766

         7              9    1735575      6868495

         7             10    1723305      6868495

         7             11    1737093      6868495

         7             12    1672522      6868495

         8             13    1516776      6854731

         8             14    1814327      6854731

         8             15    1761814      6854731

         8             16    1761814      6854731

         9             17    1710831      6739374

         9             18    1625456      6739374

         9             19    1645204      6739374

         9             20    1757883      6739374

        10             21    1542152      6238901

        10             22    1468316      6238901

        10             23    1443837      6238901

        10             24    1784596      6238901

The value for the REGION_SALES column is the same for all salespeople in the same region. In the next section, you will see two different approaches for using this information to generate percentage calculations.

14.4.2 RATIO_TO_REPORT

One of the more common uses of reporting functions is to generate the value of the denominator for performance calculations. With the query from the previous section, for example, the next logical step would be to divide each salesperson's total sales (SP_SALES) by the total region sales (REGION_SALES) to determine what ratio of the total region sales can be attributed to each salesperson. One option is to use the reporting function as the denominator in the percentage calculation, as in:

SELECT region_id, salesperson_id, 

  SUM(tot_sales) sp_sales,

  ROUND(SUM(tot_sales) /

    SUM(SUM(tot_sales)) OVER (PARTITION BY region_id), 

    2) percent_of_region

FROM orders

WHERE year = 2001

GROUP BY region_id, salesperson_id

ORDER BY region_id, salesperson_id;



REGION_ID  SALESPERSON_ID   SP_SALES PERCENT_OF_REGION

---------- -------------- ---------- -----------------

         5              1    1927580               .29

         5              2    1461898               .22

         5              3    1501039               .23

         5              4    1695124               .26

         6              5    1688252               .27

         6              6    1392648               .22

         6              7    1458053               .23

         6              8    1768813               .28

         7              9    1735575               .25

         7             10    1723305               .25

         7             11    1737093               .25

         7             12    1672522               .24

         8             13    1516776               .22

         8             14    1814327               .26

         8             15    1761814               .26

         8             16    1761814               .26

         9             17    1710831               .25

         9             18    1625456               .24

         9             19    1645204               .24

         9             20    1757883               .26

        10             21    1542152               .25

        10             22    1468316               .24

        10             23    1443837               .23

        10             24    1784596               .29

Because this is such a common operation, however, Oracle has spared us the trouble by including the RATIO_TO_REPORT function. The RATIO_TO_REPORT function allows you to calculate each row's contribution to either the entire result set, or some subset of the result set if the PARTITION BY clause is included. The next query uses RATIO_TO_REPORT to generate the percentage contribution of each salesperson to her region's total sales:

SELECT region_id, salesperson_id, 

  SUM(tot_sales) sp_sales,

  ROUND(RATIO_TO_REPORT(SUM(tot_sales)) 

    OVER (PARTITION BY region_id), 2) sp_ratio

FROM orders

WHERE year = 2001

GROUP BY region_id, salesperson_id

ORDER BY region_id, salesperson_id;



REGION_ID  SALESPERSON_ID   SP_SALES   SP_RATIO

---------- -------------- ---------- ----------

         5              1    1927580        .29

         5              2    1461898        .22

         5              3    1501039        .23

         5              4    1695124        .26

         6              5    1688252        .27

         6              6    1392648        .22

         6              7    1458053        .23

         6              8    1768813        .28

         7              9    1735575        .25

         7             10    1723305        .25

         7             11    1737093        .25

         7             12    1672522        .24

         8             13    1516776        .22

         8             14    1814327        .26

         8             15    1761814        .26

         8             16    1761814        .26

         9             17    1710831        .25

         9             18    1625456        .24

         9             19    1645204        .24

         9             20    1757883        .26

        10             21    1542152        .25

        10             22    1468316        .24

        10             23    1443837        .23

        10             24    1784596        .29