In SQL Server 6.5, there is an aggregate operator called CUBE, which can be used to produce results sets that typically are used for reports.
The CUBE operator is an additional switch in the GROUP BY clause in the SELECT statement. For additional information about the SELECT statement, see SELECT Statement in this document and in the Microsoft SQL Server Transact-SQL Reference.
The CUBE operator is used with an aggregate function to generate additional rows in a results set. Columns included in the GROUP BY clause are cross-referenced to produce a superset of groups. The aggregate function specified in the select_list is applied to these groups to produce summary values for the additional super-aggregate rows. The number of extra groups in the results set is determined by the number of columns included in the GROUP BY clause.
The aggregate function used in the SELECT statement is applied to the cross-referenced columns for the super-aggregate calculation. The CUBE operator can be applied to all aggregate functions, including AVG, SUM, MAX, MIN, and COUNT.
In fact, every possible combination of the columns or expressions in the GROUP BY clause is used to produce super-aggregates. If you have n columns or expressions, there are 2 (n) -1 possible super-aggregate combinations. Mathematically, these combinations form an n-dimensional cube, which is how the operator got its name.
The ROLLUP operator, which produces a summary of aggregates, has also been added in SQL Server 6.5. It is useful when only summary information is needed and a full CUBE provides extraneous data. For more information about the ROLLUP operator, see ROLLUP Operator.
The following are guidelines for using the CUBE operator:
You can use the CUBE operator to present cross-referenced information without having to write additional procedures. Consider a personnel department report that includes information such as job titles, home addresses, and modes of transportation to work. The SELECT statement reports on these records for each employee. By introducing the CUBE operator and the COUNT aggregate function, you can obtain statistics such as the number of people who drive to work and from what locations, and whether their jobs may be related to where they live or how they get to work.
You might also apply the CUBE operator to a company sales management report that records sales personnel, customers, products, and quantities sold of each product. A SELECT statement used with the SUM aggregate function produces a report of how many of each product were sold, to which customers, and by which sales persons.
The CUBE operator expands the information returned by cross-referencing and reporting data such as the products that particular customers frequently buy, which sales personnel sell the most of a particular product, and which products are the most popular. By using application or programming tools, this information can then be fed into charts and graphs that convey results and relationships visually and effectively.
This example demonstrates the results set from a SELECT statement that uses the CUBE operator. The SELECT statement covers a one-to-many relationship between book titles and the quantity sold of each book. By using the CUBE operator, the statement returns an extra row.
SELECT title, "qty" = SUM(qty) FROM sales, titles WHERE sales.title_id = titles.title_id GROUP BY title WITH CUBE
This is the results set:
title qty -------------------------------------------------- ----------- But Is It User Friendly? 30 Computer Phobic AND Non-Phobic Individuals: Behavi 20 Cooking with Computers: Surreptitious Balance Shee 25 Emotional Security: A New Algorithm 25 Fifty Years in Buckingham Palace Kitchens 20 Is Anger the Enemy? 108 Life Without Fear 25 Onions, Leeks, and Garlic: Cooking Secrets of the 40 Prolonged Data Deprivation: Four Case Studies 15 Secrets of Silicon Valley 50 Silicon Valley Gastronomic Treats 10 Straight Talk About Computers 15 Sushi, Anyone? 20 The Busy Executive's Database Guide 15 The Gourmet Microwave 40 You Can Combat Computer Stress! 35 (null) 493 (17 row(s) affected)
The null value represents all values in the title column. The results set returns values for the quantity sold of each title and the total quantity sold of all titles. For this particular example, applying the CUBE operator or ROLLUP operator returns the same result.
The SELECT statement returns the publication name, title, and quantity of books sold. The GROUP BY clause in this example includes two columns called pub_name and title. There are also two one-to-many relationships between publishers and titles and between titles and sales.
By using the CUBE operator, the results set contains more detailed information about the quantities of titles sold by publishers. The null value represents all values in the title column.
SELECT pub_name, title, "qty" = SUM(qty) FROM sales, titles, publishers WHERE sales.title_id = titles.title_id AND publishers.pub_id = titles.pub_id GROUP BY pub_name, title WITH CUBE
This is the results set:
pub_name title qty -------------------- ---------------------------------------- ------ Algodata Infosystems But Is It User Friendly? 30 Algodata Infosystems Cooking with Computers: Surreptitious Ba 25 Algodata Infosystems Secrets of Silicon Valley 50 Algodata Infosystems Straight Talk About Computers 15 Algodata Infosystems The Busy Executive's Database Guide 15 Algodata Infosystems (null) 135 Binnet & Hardley Computer Phobic AND Non-Phobic Individu 20 Binnet & Hardley Fifty Years in Buckingham Palace Kitche 20 Binnet & Hardley Onions, Leeks, and Garlic: Cooking Secr 40 Binnet & Hardley Silicon Valley Gastronomic Treats 10 Binnet & Hardley Sushi, Anyone? 20 Binnet & Hardley The Gourmet Microwave 40 Binnet & Hardley (null) 150 New Moon Books Emotional Security: A New Algorithm 25 New Moon Books Is Anger the Enemy? 108 New Moon Books Life Without Fear 25 New Moon Books Prolonged Data Deprivation: Four Case St 15 New Moon Books You Can Combat Computer Stress! 35 New Moon Books (null) 208 (null) (null) 493 (null) But Is It User Friendly? 30 (null) Computer Phobic AND Non-Phobic Individua 20 (null) Cooking with Computers: Surreptitious Ba 25 (null) Emotional Security: A New Algorithm 25 (null) Fifty Years in Buckingham Palace Kitche 20 (null) Is Anger the Enemy? 108 (null) Life Without Fear 25 (null) Onions, Leeks, and Garlic: Cooking Secre 40 (null) Prolonged Data Deprivation: Four Case St 15 (null) Secrets of Silicon Valley 50 (null) Silicon Valley Gastronomic Treats 10 (null) Straight Talk About Computers 15 (null) Sushi, Anyone? 20 (null) The Busy Executive's Database Guide 15 (null) The Gourmet Microwave 40 (null) You Can Combat Computer Stress! 35 (36 row(s) affected)
Increasing the number of columns in the GROUP BY clause illustrates why the CUBE operator is an n-dimensional operator. A GROUP BY clause that has two columns returns three more groups when the CUBE operator is used.
The results set is grouped by the publisher name and then by the book title. The quantity of each title sold by each publisher is listed in the right-hand column.
The (null) value in the title column stands for "all titles." See the GROUPING function later in this section for information about how to differentiate specific values and all values in the results set. The CUBE operator returns these groups of information from one SELECT statement:
Each column referenced in the GROUP BY clause has been cross-referenced with all other columns in the GROUP BY clause and the SUM aggregate has been reapplied, which produces additional rows in the results set. Information returned in the results set grows n-dimensionally along with the number of cross-referenced columns in the GROUP BY clause.
Note Ensure that the columns following the GROUP BY clause have meaningful, real-life relationships with each other. For example, if you were to use au_fname and au_lname, the CUBE operator would return irrelevant information such as the number of books sold by authors with the same first name. Using the CUBE operator on a real-life hierarchy, such as yearly sales and quarterly sales, produces meaningless rows in the results set and it is more efficient to use the ROLLUP operator.
In this example, the GROUP BY clause contains three columns that are cross-referenced by the CUBE operator. Three one-to-many relationships exist between publishers and authors, between authors and titles, and between titles and sales.
By using the CUBE operator, more detailed information is returned about the quantities of titles sold by publishers.
SELECT pub_name, au_lname, title, 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 CUBE
The CUBE operator returns the following information based on the cross-referenced groupings that are returned with the CUBE operator.
Note The super-aggregate for all publishers, all titles, and all authors is greater than the total number of sales, because a number of books have more than one author.
A pattern emerges as the number of relationships grows. The pattern of values and nulls in the report shows which groups have been formed for a summary aggregate. Explicit information about the groups is provided by the GROUPING function.
The GROUPING function is used to distinguish between real NULL values in the results set and (null) values (that represent all values for a column) in the results set.
The GROUPING function can be used with the GROUPING function or with the CUBE operator. The GROUPING function is applied to a column_name in the select_list. The column_name must be one of the columns that appears in the GROUP BY clause. A value of 1 is returned when there is a (null) representing all values in the column; 0 is returned when the column contains a value. The 1 or 0 returned is a tinyint datatype.
In this example, the SELECT statement uses the SUM aggregate, the GROUP BY clause, and the CUBE operator. It also uses the GROUPING function on the two columns that are listed after the GROUP BY clause.
SELECT pub_name, GROUPING(pub_name),title, GROUPING(title), "qty" = SUM(qty) FROM sales, titles, publishers WHERE sales.title_id = titles.title_id AND publishers.pub_id = titles.pub_id GROUP BY pub_name, title WITH CUBE
The results set has two columns containing 0 and 1 values, which are produced by the GROUPING(pub_name) and GROUPING(title) expressions. The value 1 appears when the column contains a (null). The GROUPING function returns a 1 when the values in that particular column have been grouped together by the CUBE operator.
This is the results set:
pub_name title qty -------------------- --- ------------------------- --- ----------- Algodata Infosystems 0 But Is It User Friendly? 0 30 Algodata Infosystems 0 Cooking with Computers: S 0 25 Algodata Infosystems 0 Secrets of Silicon Valley 0 50 Algodata Infosystems 0 Straight Talk About Compu 0 15 Algodata Infosystems 0 The Busy Executive's Data 0 15 Algodata Infosystems 0 (null) 1 135 Binnet & Hardley 0 Computer Phobic AND Non-P 0 20 Binnet & Hardley 0 Fifty Years in Buckingham 0 20 Binnet & Hardley 0 Onions, Leeks, and Garlic 0 40 Binnet & Hardley 0 Silicon Valley Gastronomi 0 10 Binnet & Hardley 0 Sushi, Anyone? 0 20 Binnet & Hardley 0 The Gourmet Microwave 0 40 Binnet & Hardley 0 (null) 1 150 New Moon Books 0 Emotional Security: A New 0 25 New Moon Books 0 Is Anger the Enemy? 0 108 New Moon Books 0 Life Without Fear 0 25 New Moon Books 0 Prolonged Data Deprivatio 0 15 New Moon Books 0 You Can Combat Computer S 0 35 New Moon Books 0 (null) 1 208 (null) 1 (null) 1 493 (null) 1 But Is It User Friendly? 0 30 (null) 1 Computer Phobic AND Non-P 0 20 (null) 1 Cooking with Computers: S 0 25 (null) 1 Emotional Security: A New 0 25 (null) 1 Fifty Years in Buckingham 0 20 (null) 1 Is Anger the Enemy? 0 108 (null) 1 Life Without Fear 0 25 (null) 1 Onions, Leeks, and Garlic 0 40 (null) 1 Prolonged Data Deprivatio 0 15 (null) 1 Secrets of Silicon Valley 0 50 (null) 1 Silicon Valley Gastronomi 0 10 (null) 1 Straight Talk About Compu 0 15 (null) 1 Sushi, Anyone? 0 20 (null) 1 The Busy Executive's Data 0 15 (null) 1 The Gourmet Microwave 0 40 (null) 1 You Can Combat Computer S 0 35 (36 row(s) affected)