ASA SQL User's Guide

Using OLAP

Using ROLLUP

The ROLLUP operation provides subtotals of aggregate rows. It adds subtotal rows into the result sets of queries with GROUP BY clauses. ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns. Use the ROLLUP operation when you want a result set showing totals and subtotals.

GROUP BY ROLLUP (A, B, C);

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

Example 1 

In the example that follows, the query returns data that summarizes the number of sales orders by year and quarter.

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

The table that follows represents the result set from the query. The subtotal rows are highlighted in the result set. Each subtotal row has a NULL in the column or columns over which the subtotal is computed.

The row marked [1] represents the total number of orders across both years (2000, 2001) and all quarters. This row has NULL in both the Year and Quarter columns and is the row where all columns were excluded from the prefix.

The rows highlighted row [2] represent the total number of orders in the years 2000 and 2001, respectively. Both rows have NULL in the Quarter column because the values in that column are rolled up to give a subtotal for Year.

The remaining rows marked [3] provide summary information by giving the total number of orders for each quarter in both years.

Example 2 

Here is another example of the ROLLUP operation that returns a slightly more complicated result set than the first example. The result set summarizes the number of sales orders by year, quarter, and region. In this example, only the first and second quarters and two selected regions (Canada and the Eastern region) are examined.

SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, region, COUNT (*) AS Orders
FROM sales_order
WHERE region IN ('Canada', 'Eastern') AND quarter IN ('1', '2')
GROUP BY ROLLUP (Year, Quarter, Region)
ORDER BY Year, Quarter, Region

The table that follows represents the result set from the query. Each subtotal row has a NULL in the column or columns over which the subtotal is computed.

The first row [1] is an aggregate over all rows, and has NULL in the Year, Quarter, and Region columns. The value in the Orders column of this row represents the total number of orders in Canada and the Eastern region in Quarters 1 and 2 in the years 2000 and 2001.

The rows marked [2] represent the total number of sales orders in each year (2000) and (2001) in Quarters 1 and 2 in Canada and the Eastern region. The values of these rows [2] are equal to the grand total represented in row [1].

The rows marked [3] provide data about the total number of orders for the given year and quarter by region.

The rows marked [4] provide data about the total number of orders for each year, each quarter, and each region in the result set.

Understanding ROLLUP and subtotal rows 

ROLLUP is equivalent to a UNION of a set of GROUP BY queries. The result sets of the following queries are identical. The result set of GROUP BY A, B consists of subtotals over all those rows in which A and B are held constant. To make a union possible, column C is assigned NULL.

SELECT A, B, C, 
     SUM( D )
FROM T1
GROUP BY ROLLUP 
     (A, B, C);

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

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