There are times when we need not just the total value for the column, but subtotal values depending on different column values.\u00a0 Oracle supports subtotal operators through the three key operators they are CUBE, ROLLUP,\u00a0<\/strong>and\u00a0GROUPING SETS.<\/strong> Below are examples of each one and how best to use them.<\/p>\n <\/p>\n Cube operator will provide subtotals for each interval of the non-aggregated columns in the SELECT<\/strong> statement.<\/p>\n The above example aggregates the extcost and quantity columns.\u00a0 And will provide not all the total values but the subtotal values for each month in calmonth and each value in addrcatcade1 column.<\/p>\n ROLLUP<\/strong> aggregates in a hierarchical method.\u00a0 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.<\/p>\n The above example will show the totals for the column extcost and quantiy; and subtotals for each level of company and bpname. In the\u00a0ROLLUP<\/strong> 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.\u00a0 However it will not show a subtotal for levels of bpname by itself.\u00a0 If you need to see subtotals for each level of bpname you would either use CUBE<\/strong> or GROUPING SETS<\/strong>.<\/p>\n GROUPING SETS<\/strong> are the most flexible of the subtotal operators where you can specify subtotals you want to see.<\/p>\n In the above example, in the\u00a0GROUPING SETS<\/strong> operator you specify which columns you want to calculate subtotals.\u00a0 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\u00a0GROUPING SETS<\/strong> operator.\u00a0 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.\u00a0 However if you are specifying subtotals be calculated using values from two or more columns they need to enclosed in parenthesis.<\/p>\n I hope you found the above informative, let me know in the comments below.<\/p>\nCube<\/h5>\n
SELECT calmonth\r\n ,addrcatcode1\r\n ,SUM(extcost) AS sum_extcost\r\n ,SUM(quantity) AS sum_quantity\r\n \r\nFROM inventory_fact\r\n INNER JOIN date_dim\r\n ON inventory_fact.datekey = date_dim.datekey\r\n INNER JOIN cust_vendor_dim\r\n ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey\r\nWHERE transtypekey = 5 -- 'Sales Order Shipment'\r\n AND date_dim.calyear = 2011\r\nGROUP BY CUBE(calmonth\r\n ,addrcatcode1\r\n )\r\n;<\/pre>\n
Rollup<\/h5>\n
SELECT companyname\r\n ,bpname\r\n ,SUM(extcost) AS sum_extcost\r\n ,SUM(quantity) AS sum_quantity\r\nFROM inventory_fact\r\n INNER JOIN branch_plant_dim\r\n ON inventory_fact.BRANCHPLANTKEY = branch_plant_dim.BRANCHPLANTKEY\r\n INNER JOIN company_dim\r\n ON branch_plant_dim.companykey = company_dim.COMPANYKEY\r\nWHERE transtypekey = 2 -- 'Inventory Transfer'\r\nGROUP BY ROLLUP(companyname\r\n ,bpname)\r\nORDER BY companyname, bpname\r\n;<\/pre>\n
Grouping Sets<\/h5>\n
SELECT calquarter\r\n ,zip\r\n ,cust_vendor_dim.name\r\n ,SUM(extcost) AS sum_extcost\r\n ,COUNT(inventorykey) AS count_transactions\r\n\r\nFROM inventory_fact\r\n INNER JOIN date_dim\r\n ON inventory_fact.datekey = date_dim.datekey\r\n INNER JOIN cust_vendor_dim\r\n ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey\r\nWHERE transtypekey = 5 -- 'Sales Order Shipment'\r\n AND date_dim.calyear BETWEEN 2011 AND 2012\r\nGROUP BY GROUPING SETS (\r\n (calquarter,zip,cust_vendor_dim.NAME)\r\n ,(calquarter, zip)\r\n ,(CalQuarter,cust_vendor_dim.Name) \r\n ,(cust_vendor_dim.Name,Zip) \r\n ,CalQuarter\r\n ,Zip\r\n ,cust_vendor_dim.Name\r\n ,())\r\n;<\/pre>\n