GROUP BY clause extensions

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

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 (order_date) Year, quarter(order_date)
  Quarter, COUNT(*) Orders 
FROM alt_sales_order 
GROUP BY ROLLUP(Year, Quarter) 
ORDER BY Year, Quarter

The query’s two prefixes are:

NoteThe GROUP BY list contains the same number of prefixes as items.