Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

T-SQL Tuesday #016 - Summarizing data using GROUPING SETS()

Mar 8 2011 5:21AM by Jacob Sebastian   

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
*/

Tags: TSQL, SQL SERVER, BRH, #TSQL, #SQL Server, TSQL TUESDAY,


Jacob Sebastian
1 · 100% · 32235
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

11  Comments  

  • Nice article!

    commented on Mar 8 2011 9:26PM
    Naomi
    31 · 6% · 1776
  • Here's another example below:

    Normal Grouping: (group columns together)

    SELECT City, StateProvince, Country, Sum(Amount) AS Amount FROM sales GROUP BY City, StateProvince, Country, Amount

    ===========================================================

    GroupingSets Grouping: (group columns separately)

    USE TempDB GO

    SET NOCOUNT ON

    IF EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID (N'[dbo].[Sales]') AND type in (N'U'))

    DROP TABLE [dbo].[Sales] GO CREATE TABLE [dbo].Sales NOT NULL, [City] varchar NOT NULL, [StateProvince] varchar NOT NULL, [Country] varchar NOT NULL, [Amount] [money] NOT NULL, CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ([ID] ASC) ) ON [PRIMARY]

    INSERT INTO Sales (City, StateProvince, Country, Amount) VALUES
    ('Dallas', 'Texas', 'USA', '25.00'), ('Dallas', 'Texas', 'USA', '10.00'), ('Dallas', 'Texas', 'USA', '5.00'), ('Richardson', 'Texas', 'USA', '25.00'), ('Richardson', 'Texas', 'USA', '10.00'), ('New York', 'New York', 'USA', '25.00'), ('Miami', 'Florida', 'USA', '10.00'), ('San Francisco', 'California', 'USA', '5.00'), ('Montreal', 'Quebec', 'Canada', '5.00'), ('Montreal', 'Quebec', 'Canada', '25.00'), ('Edmonton', 'Alberta', 'Canada', '10.00')

    SELECT * FROM sales

    -- Group by City SELECT City, Sum(Amount) AS Amount FROM sales GROUP BY City

    -- Group by State SELECT StateProvince, Sum(Amount) AS Amount FROM sales GROUP BY StateProvince

    -- Group by Country SELECT Country, Sum(Amount) AS Amount FROM sales GROUP BY Country

    -- Sum of all sales SELECT Sum(Amount) AS Amount FROM sales

    -- GROUPING SETS SELECT City, StateProvince, Country, Sum(Amount) AS Amount FROM sales GROUP BY GROUPING SETS ( (City), (StateProvince), (Country), () ) ORDER BY City, StateProvince, Country

    commented on Mar 22 2011 5:33PM
    kevin_nikolai
    300 · 0% · 146
  • Thanks, great article...so detailed. Grouping Set is un clear and not being used regularly.

    commented on Jan 19 2012 9:24PM
    Jason Yousef
    156 · 1% · 319
  • Thanks Naomi, Jason and Kevin.

    commented on Jan 20 2012 11:55AM
    Jacob Sebastian
    1 · 100% · 32235
  • Thanks for your great article

    commented on Feb 25 2012 2:38AM
    Guru Samy
    9 · 16% · 5043
  • hello, Your post has been just wonderful i have a similar situation, i have the entire SP results the way i need only thing is i need to add grand totals at the end if the report based on items. im on SQL 2005... kindly guide and help me

    rgds hari vaag

    commented on Mar 27 2012 10:38PM
    harivaag
    3071 · 0% · 2
  • did you try the compute by example I mentioned above? http://beyondrelational.com/modules/2/blogs/28/posts/10469/tsql-lab-4-how-to-add-a-total-line-to-the-query-result.aspx

    commented on Mar 28 2012 3:26AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hello,

    First of all I would like to thank you for all the examples...They are very useful.

    I want to ask something regarding the example: Generating a Total Row per Customer

    I want to extract the same information with your example with the following limitation: sum (amount) per customer > 'something'

    In your example if i add the following: HAVING SUM (AMOUNT) > '500'

    the result will be :

    Jacob                All Items            792.50
    

    but I want to extract all the records which, when they aggregated exceed the '500'

    Jacob                Item 1               312.50 
    Jacob                Item 2               480.00
    

    and not the :

    Jacob                All Items            792.50
    

    Could you please advise?

    Thank you in advance, Regards, potirakis8

    commented on Apr 26 2012 7:46AM
    potirakis8
    637 · 0% · 55
  • which specific example are you trying to modify/alter?

    commented on Apr 26 2012 8:04AM
    Jacob Sebastian
    1 · 100% · 32235
  • I want to ask something regarding the example: Generating a Total Row per Customer

    commented on Apr 26 2012 8:08AM
    potirakis8
    637 · 0% · 55
  • A quick and dirty way of doing it is as follows.

    ;WITH cte AS (
    	SELECT 
    		*, 
    		SUM(Quantity * PricePerCase) OVER(PARTITION BY CustomerName) AS Tot
    	FROM Orders  o
    )
    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 cte
    WHERE tot > 500
    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
    */
    
    commented on Apr 26 2012 9:11AM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

"T-SQL Tuesday #016 - Summarizing data using GROUPING SETS()" rated 5 out of 5 by 3 readers
T-SQL Tuesday #016 - Summarizing data using GROUPING SETS() , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]