The ROLLUP operator is an aggregate operator that delivers aggregates and super-aggregates for elements within a GROUP BY statement. The ROLLUP operator is applicable to cumulative aggregates such as running sums or running averages. It differs from the CUBE operator only in that it is sensitive to the column's position in the GROUP BY clause. Aggregate groupings are made up of columns to the right of the current column value.
The ROLLUP operator can be used by report writers to extract statistics and summary information from results sets. The cumulative aggregates, such as running sums, can be used in reports, charts, and graphs.
The ROLLUP operator creates groupings by moving in only one direction, from right to left, along the list of columns in the GROUP BY clause. It then applies the aggregate function to these groupings. The CUBE operator creates all combinations of groupings from the list of columns in the GROUP BY clause.
This example uses a SELECT query that contains an aggregate function and a GROUP BY clause, which lists pub_name, au_lname, and title, in that order.
SELECT pub_name, au_lname, title, "SUM" = SUM(qty) FROM publishers, authors, titles, titleauthor, sales WHERE publishers.pub_id = titles.pub_id AND authors.au_id = titleauthor.au_id AND titleauthor.title_id = titles.title_id AND titles.title_id = sales.title_id GROUP BY pub_name, au_lname, title WITH ROLLUP
By using the ROLLUP operator, these groupings are created by moving right to left along the list of columns:
pub_name au_lname title SUM(qty) pub_name au_lname (null) SUM(qty) pub_name (null) (null) SUM(qty) (null) (null) (null) SUM(qty)
The (null) value represents all values for that column.
If you use the SELECT statement without the ROLLUP operator, the statement creates a single grouping. The query returns a sum value for each unique combination of pub_name, au_lname, and title.
pub_name au_lname title SUM(qty)
Compare these examples with the groupings created by using the CUBE operator on the same query:
pub_name au_lname title SUM(qty) pub_name au_lname (null) SUM(qty) pub_name (null) (null) SUM(qty) (null) (null) (null) SUM(qty) (null) au_lname title SUM(qty) (null) au_lname (null) SUM(qty) pub_name (null) title SUM(qty) (null) (null) title SUM(qty)
The groupings correspond to the information returned in the results set. Where you see (null) in the results set, it represents all values in the column. The ROLLUP operator returns the following data when the columns (pub_name, au_lname, title) are in the order listed in the GROUP BY clause:
This is the results set returned by the SELECT query:
pub_name au_lname title SUM ----------------- ------------ ------------------------------------ --- Algodata Infosys Bendit The Busy Executive's Database Guide 15 Algodata Infosys Bendit (null) 15 Algodata Infosys Carson (null) 30 Algodata Infosys Dull Secrets of Silicon Valley 50 Algodata Infosys Dull (null) 50 Algodata Infosys Green The Busy Executive's Database Guide 15 Algodata Infosys Green (null) 15 Algodata Infosys Hunter Secrets of Silicon Valley 50 Algodata Infosys Hunter (null) 50 Algodata Infosys MacFeather Cooking with Computers: Surre 25 Algodata Infosys MacFeather (null) 25 Algodata Infosys O'Leary Cooking with Computers: Surre 25 Algodata Infosys O'Leary (null) 25 Algodata Infosys Straight Straight Talk About Computers 15 Algodata Infosys Straight (null) 15 Algodata Infosys (null) (null) 225 Binnet & Hardley Blotchet-Hal Fifty Years in Buckingham Palace Ki 20 Binnet & Hardley Blotchet-Hal (null) 20 Binnet & Hardley DeFrance The Gourmet Microwave 40 Binnet & Hardley DeFrance (null) 40 Binnet & Hardley del Castillo Silicon Valley Gastronomic Treats 10 Binnet & Hardley del Castillo (null) 10 Binnet & Hardley Gringlesby Sushi, Anyone? 20 Binnet & Hardley Gringlesby (null) 20 Binnet & Hardley Karsen Computer Phobic AND Non-Phobic Indiv 20 Binnet & Hardley Karsen (null) 20 Binnet & Hardley MacFeather Computer Phobic AND Non-Phobic Indiv 20 Binnet & Hardley MacFeather (null) 20 Binnet & Hardley O'Leary Sushi, Anyone? 20 Binnet & Hardley O'Leary (null) 20 Binnet & Hardley Panteley Onions, Leeks, and Garlic: Cooking 40 Binnet & Hardley Panteley (null) 40 Binnet & Hardley Ringer The Gourmet Microwave 40 Binnet & Hardley Ringer (null) 40 Binnet & Hardley Yokomoto Sushi, Anyone? 20 Binnet & Hardley Yokomoto (null) 20 Binnet & Hardley (null) (null) 250 New Moon Books Green You Can Combat Computer Stress! 35 New Moon Books Green (null) 35 New Moon Books Locksley Emotional Security: A New Algorithm 25 New Moon Books Locksley (null) 25 New Moon Books Ringer Is Anger the Enemy? 216 New Moon Books Ringer Life Without Fear 25 New Moon Books Ringer (null) 241 New Moon Books White Prolonged Data Deprivation: Four 15 New Moon Books White (null) 15 New Moon Books (null) (null) 316 (null) (null) (null) 791 (49 row(s) affected)
For more information about the ROLLUP operator, see SELECT Statement.
The GROUPING function can be used with the ROLLUP operator or with the CUBE operator. You can apply this function to one of the columns in the select_list and it returns either 1 or 0 depending upon whether the column is grouped by the ROLLUP operator. For more information about the GROUPING function, see SELECT Statement or CUBE Operator.