Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » OLAP Support » GROUP BY clause extensions » Using ROLLUP and CUBE

Using ROLLUP Next Page

Using CUBE


As an alternative to the hierarchical grouping pattern provided by the ROLLUP clause, you can also create a data cube, that is, an n-dimensional summarization of the input using every possible combination of GROUP BY expressions, using the CUBE clause. The CUBE clause results in a product set of all possible combinations of elements from each set of values. This can be very useful for complex data analysis.

If there are n GROUPING expressions of the form (X1,X2, . . .,Xn) in a CUBE clause, then CUBE generates 2n grouping sets as:

{(), (X1), (X1,X2), (X1,X2,X3), . . . , (X1,X2,X3, . . .,Xn),
(X2), (X2,X3), (X2,X3,X4), . . . , (X2,X3,X4, . . . , Xn), . . . , (Xn)}.
Example

The following query summarizes sales orders by year, by quarter, and quarter within year, and yields the result set shown in the table below:

SELECT QUARTER( OrderDate ) AS Quarter,
       YEAR( OrderDate ) AS Year,
       COUNT( * ) AS Orders,
       GROUPING( Quarter ) AS GQ,
       GROUPING( Year ) AS GY
FROM SalesOrders
GROUP BY CUBE ( Year, Quarter )
ORDER BY Year, Quarter;

This query returns the following results:

QuarterYearOrdersGQGY
1(NULL)(NULL)64811
21(NULL)22601
32(NULL)19601
43(NULL)10101
54(NULL)12501
6(NULL)200038010
7120008700
8220007700
9320009100
104200012500
11(NULL)200126810
121200113900
132200111900
14320001000

The first row in the result set shows the grand total (648) of all orders, for all quarters, for years 2000 and 2001 combined.

Rows 2-5 summarize sales orders by calendar quarter in any year.

Rows 6 and 11 show total Orders for years 2000, and 2001, respectively.

Rows 7-10 and rows 12-14 show the quarterly totals for years 2000, and 2001, respectively.

Note how the values returned by the GROUPING function can be used to differentiate subtotal rows from the row that contains the grand total. For rows 6 and 11, the presence of NULL in the Quarter column, and the value of 1 in the GQ column (Grouping by Quarter), indicate that the row is a totaling of Orders in all quarters for the year.

Note

The result set generated through the use of CUBE can be very large because CUBE generates an exponential number of grouping sets. For this reason, SQL Anywhere does not permit a GROUP BY clause to contain more than 64 GROUP BY expressions. If a statement exceeds this limit, it fails with SQLCODE -944 (SQLSTATE 42WA1).

For more information about the syntax for the CUBE clause, see GROUP BY clause.

Support for T-SQL WITH CUBE syntax

Alternatively, you can also use the Transact-SQL compatible syntax, WITH CUBE, to achieve the same results as GROUP BY CUBE. However, the syntax is slightly different and you can only supply a simple GROUP BY expression list in the syntax.

The following query produces an identical result to that of the previous GROUP BY CUBE example:

SELECT QUARTER( OrderDate ) AS Quarter,
       YEAR( OrderDate ) AS Year,
       COUNT( * ) AS Orders,
       GROUPING( Quarter ) AS GQ,
       GROUPING( Year ) AS GY
FROM SalesOrders
GROUP BY Year, Quarter WITH CUBE
ORDER BY Year, Quarter;