ROLLUP and CUBE

ROLLUP and CUBE make it possible for values to be aggregated on several different axes satisfying more detailed reporting needs. ROLLUP summarizes against a hierarchy of columns used in the GROUP BY clause. CUBE groups by all combinations of the values.

ROLLUP

If we wanted to see closed deals in the pipeline aggregated by sum for the total, the region, and the sales agent, we would write the query like this:

SELECT teams.regional_office,
       pipeline.sales_agent,
       SUM(pipeline.close_value)
  FROM sales_pipeline AS pipeline, sales_teams AS teams
 WHERE sales_pipeline.sales_agent = sales_teams.sales_agent
       AND sales_pipeline.deal_stage = "Won"
 GROUP BY ROLLUP(teams.regional_office, pipeline.sales_agent)
 ORDER BY teams.regional_office, pipeline.sales_agent

And the returned data would look like this:

The row with no value in either of the first two columns is the sum of all the sales in all regions. The rows with data in the first column but not the second contain the sum for that region.

CUBE

The above example was great for ROLLUP because there was an easy hierarchy with the entire pipeline at the top, then sales divided into regions, then the regions further divided into sales agents. However if we wanted to compare the sales numbers for products and sales agents, there isn’t a logical hierarchy: product is not a subcategory of sales agent, and sales agent is not a subcategory of product. A better result combination then might be to return all possible combinations of of the sums of products and sales agents. We would get those combinations using CUBE:

SELECT product,
       sales_agent,
       SUM(close_value)
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY CUBE(product, sales_agent)
 ORDER BY product, sales_agent

The data returned from this query shows the sum of all the sales, followed by all sales by sales agent. Then the product info is folded in and the sum of the first product sales is given, followed by the the sum of that product’s sales by sales agent. The product sum followed by product sum by sales agent pattern continues for the rest of the results table.

If one of the columns that you are rolling up contains nulls then you may see ambiguous rows where you cannot tell if the null in that column is a value in a rows, or is due to the rollup itself. In that case you can use the GROUPING aggregation to disambiguate. Add a GROUPING as another column, referencing the column you are grouping by. A one in the GROUPING column means the value is due to rollup. Otherwise a zero will be shown:

query:

 SELECT sales_agent,
        account,
        GROUPING(sales_agent),
        GROUPING(account),
        SUM(order_value)
   FROM apr_2017_orders
  GROUP BY ROLLUP(sales_agent, account)
  ORDER BY sales_agent, account

result: |sales_agent|account|grouping|grouping_2|sum| |-|-|-|-|-| |sales_agent|account|grouping|grouping_2|sum| |-|-|-|-|-| |Boris Faz|Zencorporation|0|0|560| |Boris Faz|Zotware|0|0|59| |Boris Faz||0|1|18957| |Cassey Cress|Donquadtech|0|0|59| |Cassey Cress|Gekko & Co|0|0|1114| |Cassey Cress|Initech|0|0|3275|