Mastering Oracle SQL

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 the entire partition (or the entire result set, if no partitions are specified). Therefore, anything that can be accomplished using a reporting function could also be accomplished using a windowing function with an unbounded window, although it would 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 ...