GROUP BY Clause Extensions

Extensions to the GROUP BY clause let application developers write complex SQL statements that:

Partition the input rows in multiple dimensions and combine multiple subsets of result groups.
Create a “data cube,” providing a sparse, multi dimensional result set for data mining analyses.
Create a result set that includes the original groups, and optionally includes a subtotal and grand-total row.

OLAP Grouping() operations, such as ROLLUP and CUBE, can be conceptualized as prefixes and subtotal rows.

Prefixes

A list of prefixes is constructed for any query that contains a GROUP BY clause. A prefix is a subset of the items in the GROUP BY clause and is constructed by excluding one or more of the rightmost items from those in the query’s GROUP BY clause. The remaining columns are called the prefix columns.

ROLLUP example 1—In the following ROLLUP example query, the GROUP BY list includes two variables, Year and Quarter:

SELECT year (OrderDate) AS Year, quarter(OrderDate)
  AS Quarter, COUNT(*) Orders 
FROM SalesOrders 
GROUP BY ROLLUP(Year, Quarter) 
ORDER BY Year, Quarter

The query’s two prefixes are:

Exclude Quarter – the set of prefix columns contains the single column Year.
Exclude both Quarter and Year – there are no prefix columns.

Note: The GROUP BY list contains the same number of prefixes as items.