Oracle PL/SQL Subtotal Operators

There are times when we need not just the total value for the column, but subtotal values depending on different column values.  Oracle supports subtotal operators through the three key operators they are CUBE, ROLLUP, and GROUPING SETS. Below are examples of each one and how best to use them.

Cube

Cube operator will provide subtotals for each interval of the non-aggregated columns in the SELECT statement.

The above example aggregates the extcost and quantity columns.  And will provide not all the total values but the subtotal values for each month in calmonth and each value in addrcatcade1 column.

Rollup

ROLLUP aggregates in a hierarchical method.  It will show totals for the entire column and each level in the hierarchy. The hierarchy doesn’t have to be defined in the database.

The above example will show the totals for the column extcost and quantiy; and subtotals for each level of company and bpname. In the ROLLUP operator you specify the hierarchy order. The first column listed is the highest level of the hierarchy and each subsequent column will be a lower level of the hierarchy.  However it will not show a subtotal for levels of bpname by itself.  If you need to see subtotals for each level of bpname you would either use CUBE or GROUPING SETS.

Grouping Sets

GROUPING SETS are the most flexible of the subtotal operators where you can specify subtotals you want to see.

In the above example, in the GROUPING SETS operator you specify which columns you want to calculate subtotals.  You can specify as many or as few grouping sets as you want. If you want to see a total for the entire column then use an empty tuple, “()” in the list of tuples provided to the GROUPING SETS operator.  If you are calculating the subtotal based on the values in one column you can include or exclude the parenthesis. In the example above you can see this with ,calQuarter, Zip, cust_vendor_dim.Name.  However if you are specifying subtotals be calculated using values from two or more columns they need to enclosed in parenthesis.

I hope you found the above informative, let me know in the comments below.

— michael.data@eipsoftware.com