I have been watching the TSQL Tuesday Blog Parties for quite some time and this is the first time I am participating in it. T-SQL Tuesday #16 is hosted by Jes Schultz Borland and the topic is Aggregation.
Aggregating and summarizing data is a common business requirement. Very often we come across requirements to calculate the total values presented in one or more columns on a web page. Some times the requirement may be bit more complicated such as to calculate multiple levels of sub-totals along with a grand total.
Usually this type of tasks are efficiently done by reporting tools. Most reporting tools are equipped with the functionality to partition and summarize data based on custom user requirements. Very often I see questions asking for help to write TSQL queries that returns data in specific shape and format so that the client application can directly display the information on a web page. My first answer used to be an advice to handle this type of requirements in the presentation layer of the application, until I came across a real requirement myself, a few years back.
The application had a few web pages which display various sales data in a grid control with multiple levels of sub-totals and totals. The web page had a huge chunk of code written to correctly calculate the sub totals of each levels and display them at the right position within the grid control. The page was very slow and the task was to optimize it. Moving the calculation logic into the database helped to improve performance tremendously.
There are a number of TSQL keywords available that we can use to produce sub-totals and totals along with the query results. The simplest of them is COMPUTE BY which produces a second result set with the totals of the query as given in this example. But a second result set may not be desirable on certain situations. We might need a single result set with a total row. This can be easily achieved by using using WITH ROLLUP or WITH CUBE as demonstrated here, here and here.
Enter GROUPING SETS
SQL Server 2008 introduced a new function GROUPING SETS() which gives more control over the total rows generated along with the query result. We will see a few examples to understand this. Run the following script to generate the sample table for the examples we will use in this post.
IF OBJECT_ID('Orders') IS NOT NULL DROP TABLE Orders
GO
CREATE TABLE Orders (
OrderID INT,
OrderDate DATETIME,
CustomerName VARCHAR(20),
ItemName VARCHAR(20),
Quantity INT,
PricePerCase MONEY )
GO
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
SELECT 1, '2007-08-01', 'Jacob', 'Item 1', 10, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
SELECT 1, '2007-08-01', 'Jacob', 'Item 2', 12, 15.0
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
SELECT 2, '2008-08-02', 'Jacob', 'Item 1', 15, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
SELECT 2, '2008-08-02', 'Jacob', 'Item 2', 20, 15.0
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
SELECT 1, '2008-08-01', 'Mike', 'Item 1', 6, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
SELECT 1, '2008-08-01', 'Mike', 'Item 2', 4, 11.0
Within the GROUPING SETS() function, you can define set of columns on which you need a total row to be generated. The following example shows a query using GROUPING SETS(). It does not generate any total/subtotal rows and produces the same result as a GROUP BY clause could generate.
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName))
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Mike Item 1 75.00
Jacob Item 2 480.00
Mike Item 2 44.00
*/
Generating only Subtotals and Grand Total Row
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((ItemName), (CustomerName),())
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob All Items 792.50
Mike All Items 119.00
All Customers All Items 911.50
All Customers Item 1 387.50
All Customers Item 2 524.00
*/
Generating a Total Row per Customer
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (CustomerName))
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Jacob Item 2 480.00
Jacob All Items 792.50
Mike Item 1 75.00
Mike Item 2 44.00
Mike All Items 119.00
*/
Generating a Total Row per Item
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName))
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Mike Item 1 75.00
All Customers Item 1 387.50
Jacob Item 2 480.00
Mike Item 2 44.00
All Customers Item 2 524.00
*/
Generating a Grand Total row
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), ())
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Jacob Item 2 480.00
Mike Item 1 75.00
Mike Item 2 44.00
All Customers All Items 911.50
*/
Generating Subtotal rows for Customers and a Grand Total row
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (CustomerName), ())
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Jacob Item 2 480.00
Jacob All Items 792.50
Mike Item 1 75.00
Mike Item 2 44.00
Mike All Items 119.00
All Customers All Items 911.50
*/
Generating Subtotal rows for Items and a Grand Total Row
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), ())
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Mike Item 1 75.00
All Customers Item 1 387.50
Jacob Item 2 480.00
Mike Item 2 44.00
All Customers Item 2 524.00
All Customers All Items 911.50
*/
Generating Sub total rows for Customers and Items
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), (CustomerName))
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Mike Item 1 75.00
All Customers Item 1 387.50
Jacob Item 2 480.00
Mike Item 2 44.00
All Customers Item 2 524.00
Jacob All Items 792.50
Mike All Items 119.00
*/
Generating Sub total rows for Customers and Items along with a Grand Total Row
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), (CustomerName),())
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 1 312.50
Mike Item 1 75.00
All Customers Item 1 387.50
Jacob Item 2 480.00
Mike Item 2 44.00
All Customers Item 2 524.00
All Customers All Items 911.50
Jacob All Items 792.50
Mike All Items 119.00
*/
Ordering Results
The above result set gives us sub totals by customer and by item and again a grand total row. However, the order of the rows is not good. If you want to retrieve the results in a specific order, you need to specify an ordering clause. The following query produces the results ordered by the grouping level.
SELECT
CASE
WHEN GROUPING(CustomerName) = 1 THEN 'All Customers'
ELSE CustomerName
END AS CustomerName,
CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
ELSE ItemName
END AS ItemName,
SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), (CustomerName),())
ORDER BY GROUPING(CustomerName), GROUPING(ItemName)
/*
CustomerName ItemName Amount
-------------------- -------------------- ---------------------
Jacob Item 2 480.00
Mike Item 2 44.00
Jacob Item 1 312.50
Mike Item 1 75.00
Jacob All Items 792.50
Mike All Items 119.00
All Customers Item 1 387.50
All Customers Item 2 524.00
All Customers All Items 911.50
*/