ROLLUP

ROLLUP is an extension to the GROUP BY operator that calculates multiple levels of subtotals across a specified group of dimensions, and calculates a grand total, ROLLUP. Subtotals “roll up” from the most detailed level to the grand total.

Summarizing Product Inventory

Suppose you want to summarize inventory in the Products table by name, size, and color:

SELECT Name, Size, Color, Sum(Quantity) 
FROM Products
GROUP BY ROLLUP (Name, Size, Color);

Name	Size	Color	sum(Products.Quantity)
Baseball Cap	One size fits all	Black	112
Baseball Cap	One size fits all	White	12
Baseball Cap	One size fits all	(NULL)	124
Baseball Cap	(NULL)	(NULL)	124
Shorts	Medium	Black	80
Shorts	Medium	(NULL)	80
Shorts	(NULL)	(NULL)	80
Sweatshirt	Large	Blue	32
Sweatshirt	Large	Green	39
Sweatshirt	Large	(NULL)	71
Sweatshirt	(NULL)	(NULL)	71
Tee Shirt	Medium	Orange	54
Tee Shirt	Medium	(NULL)	54
Tee Shirt	One size fits all	Black	75
Tee Shirt	One size fits all	(NULL)	75
Tee Shirt	Small	White	28
Tee Shirt	Small	(NULL)	28
Tee Shirt	(NULL)	(NULL)	157
Visor	One size fits all	Black	28
Visor	One size fits all	White	36
Visor	One size fits all	(NULL)	64
Visor	(NULL)	(NULL)	64
(NULL)	(NULL)	(NULL)	496

When processing this query, Sybase IQ first groups the data by all three specified grouping expressions (name, size, color), then for all grouping expressions except the last one (color). In the third row, NULL indicates the ROLLUP value for the color column.

ROLLUP requires an ordered list of grouping expressions as arguments. When listing groups that contain other groups, list the larger group first. You can use ROLLUP with the aggregate functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE. ROLLUP does not support COUNT DISTINCT and SUM DISTINCT, however.