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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT calmonth ,addrcatcode1 ,SUM(extcost) AS sum_extcost ,SUM(quantity) AS sum_quantity FROM inventory_fact INNER JOIN date_dim ON inventory_fact.datekey = date_dim.datekey INNER JOIN cust_vendor_dim ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey WHERE transtypekey = 5 -- 'Sales Order Shipment' AND date_dim.calyear = 2011 GROUP BY CUBE(calmonth ,addrcatcode1 ) ; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT companyname ,bpname ,SUM(extcost) AS sum_extcost ,SUM(quantity) AS sum_quantity FROM inventory_fact INNER JOIN branch_plant_dim ON inventory_fact.BRANCHPLANTKEY = branch_plant_dim.BRANCHPLANTKEY INNER JOIN company_dim ON branch_plant_dim.companykey = company_dim.COMPANYKEY WHERE transtypekey = 2 -- 'Inventory Transfer' GROUP BY ROLLUP(companyname ,bpname) ORDER BY companyname, bpname ; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT calquarter ,zip ,cust_vendor_dim.name ,SUM(extcost) AS sum_extcost ,COUNT(inventorykey) AS count_transactions FROM inventory_fact INNER JOIN date_dim ON inventory_fact.datekey = date_dim.datekey INNER JOIN cust_vendor_dim ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey WHERE transtypekey = 5 -- 'Sales Order Shipment' AND date_dim.calyear BETWEEN 2011 AND 2012 GROUP BY GROUPING SETS ( (calquarter,zip,cust_vendor_dim.NAME) ,(calquarter, zip) ,(CalQuarter,cust_vendor_dim.Name) ,(cust_vendor_dim.Name,Zip) ,CalQuarter ,Zip ,cust_vendor_dim.Name ,()) ; |
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.
Leave a Reply