Oracle – Calculating percentages or ratios in a query, RATIO_TO_REPORT()

Oracle offers an analytic function, RATIO_TO_REPORT(), that can be used to easily compute the percentage of each record to various totals returned by a query. As an example, consider an inventory count for a department store. The INVENTORY_COUNT table captures the product name, its department, and the total count for that product. Here is the table:

SQL> DESC inventory_count
 Name                Null?    Type
 ------------------- -------- -------------------
 PRODUCT                       VARCHAR2(30)
 DEPARTMENT                    VARCHAR2(30)
 COUNT                         NUMBER


SQL> SELECT department, product, count
  2  FROM inventory_count
  3  ORDER BY department, product;

DEPARTMENT                     PRODUCT                             COUNT
------------------------------ ------------------------------ ----------
CLOTHING                       PANTS                                  23
CLOTHING                       SKIRT                                  39
CLOTHING                       SOCK                                   26
HOUSEWARES                     BLENDER                                 5
HOUSEWARES                     GLASS                                  34
HOUSEWARES                     PAN                                    12
HOUSEWARES                     PLATE                                  43
HOUSEWARES                     SPOON                                  45
TOOLS                          DRILL                                  17
TOOLS                          HAMMER                                 27
TOOLS                          LADDER                                 16
TOOLS                          LEVEL                                  18
TOOLS                          SCREWDRIVER                             7

13 rows selected.

A requirement is given stating that the query should produce the count for each product as well as two additional computations:
1) Ratio of each product count in relation to the total department product count
2) Ratio of each product count to the total inventory product count

With the RATIO_TO_REPORT() function, this is easy:

SQL> SELECT
  2    department,
  3    product,
  4    count,
  5    RATIO_TO_REPORT( count ) OVER ( PARTITION BY department ) dept_perc,
  6    RATIO_TO_REPORT( count ) OVER () total_perc
  7  FROM inventory_count
  8  ORDER BY department, product;

DEPARTMENT                     PRODUCT                             COUNT  DEPT_PERC TOTAL_PERC
------------------------------ ------------------------------ ---------- ---------- ----------
CLOTHING                       PANTS                                  23 .261363636 .073717949
CLOTHING                       SKIRT                                  39 .443181818       .125
CLOTHING                       SOCK                                   26 .295454545 .083333333
HOUSEWARES                     BLENDER                                 5 .035971223 .016025641
HOUSEWARES                     GLASS                                  34 .244604317 .108974359
HOUSEWARES                     PAN                                    12 .086330935 .038461538
HOUSEWARES                     PLATE                                  43 .309352518 .137820513
HOUSEWARES                     SPOON                                  45 .323741007 .144230769
TOOLS                          DRILL                                  17         .2 .054487179
TOOLS                          HAMMER                                 27 .317647059 .086538462
TOOLS                          LADDER                                 16 .188235294 .051282051
TOOLS                          LEVEL                                  18 .211764706 .057692308
TOOLS                          SCREWDRIVER                             7 .082352941 .022435897

13 rows selected.

This output is a little messy, so next convert the ratios to percentages (multiply by 100, round to two decimal places):

SQL> SELECT
  2    department,
  3    product,
  4    count,
  5    ROUND( RATIO_TO_REPORT( count ) OVER ( PARTITION BY department ) * 100, 2 ) dept_perc,
  6    ROUND( RATIO_TO_REPORT( count ) OVER () * 100, 2 ) total_perc
  7  FROM inventory_count
  8  ORDER BY department, product;

DEPARTMENT                     PRODUCT                             COUNT  DEPT_PERC TOTAL_PERC
------------------------------ ------------------------------ ---------- ---------- ----------
CLOTHING                       PANTS                                  23      26.14       7.37
CLOTHING                       SKIRT                                  39      44.32       12.5
CLOTHING                       SOCK                                   26      29.55       8.33
HOUSEWARES                     BLENDER                                 5        3.6        1.6
HOUSEWARES                     GLASS                                  34      24.46       10.9
HOUSEWARES                     PAN                                    12       8.63       3.85
HOUSEWARES                     PLATE                                  43      30.94      13.78
HOUSEWARES                     SPOON                                  45      32.37      14.42
TOOLS                          DRILL                                  17         20       5.45
TOOLS                          HAMMER                                 27      31.76       8.65
TOOLS                          LADDER                                 16      18.82       5.13
TOOLS                          LEVEL                                  18      21.18       5.77
TOOLS                          SCREWDRIVER                             7       8.24       2.24

13 rows selected.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s