ASA SQL User's Guide

Using OLAP

Using GROUPING SETS

GROUPING SETS let you compute groups on several different sets of grouping columns in the same query. Whereas CUBE and ROLLUP add a predefined set of subtotals into the result set, GROUPING SETS lets you explicitly specify which subtotals to add. It is more complicated to use than ROLLUP or CUBE, but permits finer control over the result set. This operation is useful if you would like to return only part of the multidimensional result set of a cube. GROUPING SETS let you select which variables to compare instead of returning a comparison of all the variables like CUBE, or a hierarchical subset, like ROLLUP.

GROUP BY GROUPING SETS 
     ((A, B), (A, C), (C));

For more information about rows returned by GROUPING SETS queries, see About OLAP operations. For more information about using GROUP BY clauses in queries, see GROUP BY clause.

Example 

The following example shows how you can tailor the results that are returned from a query using GROUPING SETS. The query specifies that the result set returns subtotal rows for each year, but not for the individual quarters.

SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders 
FROM sales_order
GROUP BY GROUPING SETS ((Year, Quarter), (Year))
ORDER BY Year, Quarter

The table that follows represents the result set from the query. The subtotal rows are highlighted.

The result set includes only the subtotal rows for each year (2000 and 2001). Notice that it does not provide a grand total for the number of orders in both years and all quarters.

If you ran a query using the CUBE operator, you would get additional subtotal rows for overall orders, for each quarter (1, 2, 3, and 4), and for each year (2000 and 2001).

Understanding GROUPING SETS 

Similar to ROLLUP, the GROUPING SETS operation is equivalent to a UNION ALL of GROUP BY queries.

SELECT A, B, C, 
     SUM( D )
FROM t
GROUP BY GROUPING SETS 
     ( A, B, C )

SELECT * FROM (
 ( SELECT A, NULL, NULL, SUM( D )
FROM t GROUP BY A )
  UNION ALL
 ( SELECT NULL, B, NULL, SUM( D )
FROM t GROUP BY B )
  UNION ALL
 ( SELECT NULL, NULL, C, SUM( D )
FROM t GROUP BY C )
)

Using concatenated GROUPING SETS

SQL Anywhere Studio 9.0.2
Copyright © 1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.