The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data.
I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk.
Today, I am going to be introducing the CUBE operator. Let us start off by getting the business requirements for the query from the Sales & Marketing manager.
(S)he wants to get Adventure Works production inventory grouped by main category, sub category and item color. The basic statistics for reporting should be total items in stock, average list price per item and average cost per item.
My solution to this business problem is to use the CREATE VIEW statement to make a view named ‘vw_Inventory_Cube1’ that represents a three dimensional cube. Each of the three columns (coordinates) can be used to find the correct tile on the cube. Each tile has the basic statistics for that grouping.
Normally, the CUBE operator will return a NULL value when it is summarizing by a dimension. I am going to use the GROUPING() function to convert this value to the word ‘All’. When there is an actual NULL value for the data, I am going to convert this value to the word ‘Unknown’. I am going to use the CASE expression to implement this IF-THEN-ELSE logic.
The snippet below creates the required view.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- Use Adventure Works USE [AdventureWorks2012_CS] GO -- Delete existing view IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Production].[vw_Inventory_Cube1]') AND OBJECTPROPERTY(object_id, N'IsView') = 1) DROP VIEW [Production].[vw_Inventory_Cube1] GO -- Create a view for the inventory cube CREATE VIEW [Production].[vw_Inventory_Cube1] AS SELECT -- Main CASE WHEN (GROUPING(PC1.Name) = 1) THEN 'All' ELSE ISNULL(PC1.Name, 'Unknown') END AS MainCategory, -- Sub CASE WHEN (GROUPING(PS1.Name) = 1) THEN 'All' ELSE ISNULL(PS1.Name, 'Unknown') END AS SubCategory, -- Color CASE WHEN (GROUPING(P1.Color) = 1) THEN 'All' ELSE ISNULL(P1.Color, 'Unknown') END AS ItemColor, -- Statistics SUM(Quantity) as ItemStock , FORMAT(AVG(P1.ListPrice), 'C', 'en-us') as AvgList , FORMAT(AVG(P1.StandardCost), 'C', 'en-us') as AvgCost FROM [Production].[ProductInventory] AS PI1 LEFT JOIN [Production].[Product] AS P1 ON PI1.ProductID = P1.ProductID LEFT JOIN [Production].[ProductSubcategory] AS PS1 ON P1.ProductSubcategoryID = PS1.ProductSubcategoryID LEFT JOIN [Production].[ProductCategory] as PC1 ON PS1.ProductCategoryID = PC1.ProductCategoryID WHERE PC1.Name IS NOT NULL GROUP BY PC1.Name , PS1.Name , P1.Color WITH CUBE GO </span> |
We need to join four table together to extract the required information from the database.
The [ProductInventory] table contains inventory levels and the [Product] table has the characteristics of each item. Both the [ProductCategory] and [ProductSubcategory] tables are used to classify the products into various descriptive buckets.
I am dropping all the items that can not be categorized by using the WHERE clause. Also, we are grouping by [MainCategory], [SubCategory] and [ItemColor]. The expressions used in aggregation have no new functions to introduce.
Now that we have our view in place, we need to run two queries to retrieve the data that the Sales & Marketing manager wanted.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- Does bike color matter? SELECT SubCategory , ItemColor , ItemStock , AvgList FROM [Production].[vw_Inventory_Cube1] WHERE MainCategory = 'All' and SubCategory like '%Bikes%' ORDER BY SubCategory, ItemStock, ItemColor |
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- What is the stock level by category SELECT MainCategory , ItemStock , AvgList , AvgCost FROM [Production].[vw_Inventory_Cube1] WHERE MainCategory <> 'All' and SubCategory = 'All' and ItemColor = 'All' ORDER BY MainCategory, ItemStock |
Please note that ANSI SQL is a living work of art. The syntax has been changed to be ISO compliant so that the WITH CUBE clause might not be available in future versions of SQL Server. The new syntax combines both the clauses into one.
|
1 2 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- WITH CUBE is depreciated! GROUP BY CUBE (PC1.Name, PS1.Name, P1.Color) |
To recap, the CUBE operator calculates all permutations of the columns in the GROUP BY clause. Therefore, the more columns you add, the longer the query will take to execute.
Use the GROUPING() function to determine if a column is a summary row (ALL) or a NULL value (UNKNOWN). I suggest using a VIEW to save the TSQL in a format the can be queried to gleam information out of the CUBE.
Next time, I will be examining the GROUPING SETS operator which gives you more control over how things are summarized.
