Archive

Posts Tagged ‘grand total’

SQL Server – TSQL – GROUP BY GROUPING SETS()

12.07.2011 4 comments

By using GROUPING SETS() we can specify multiple groupings in a single query. GROUPING SETS() generates the result by producing a UNION ALL set of the result sets generated by specified grouping sets.

for example, consider below data:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      [Group], [Name], [SalesYTD]

FROM        dbo.Sales

Result Set:

Group                          Name                           SalesYTD

—————————— —————————— —————————————

North America                  Northwest                      123237.00

South America                  Northwest                      37534.00

South America                  Southwest                      39667.00

North America                  Southwest                      164232.00

 

(4 row(s) affected)

 

To summarize this data we can use GROUP BY, to summarize by more that one set we need multiple SELECTs, and UNION ALL to merge the result set:

SELECT      [Group], [Name], SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.Sales

GROUP BY [Group], [Name]

 

UNION ALL

 

SELECT      [Group], NULL, SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.Sales

GROUP BY [Group]

 

UNION ALL

 

SELECT      NULL, NULL, SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.Sales

Result Set:

Group                          Name                           Total Sales

—————————— —————————— —————————————

North America                  Northwest                      123237.00

South America                  Northwest                      37534.00

North America                  Southwest                      164232.00

South America                  Southwest                      39667.00

North America                  NULL                           287469.00

South America                  NULL                           77201.00

NULL                           NULL                           364670.00

 

(7 row(s) affected)

The above result set can be achieved by a single GROUPING SETS() clause as below:

— WITH ROLLUP Equivalent

SELECT      [Group], [Name], SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.Sales

GROUP BY GROUPING SETS (([Group], [Name]), ([Group]), ())

As the sets we have specified generate sub-total and a grand-total row, the same result set can be produced by using GROUP BY ROLLUP().

The empty set ‘( )’ in GROUPING SETS() generates grand total.

 

GROUPING SETS() can also be used to generate results produced by GROUP BY CUBE(), since we can specify all the sets generated by GROUP BY CUBE().

SELECT      [Group], [Name], SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.Sales

GROUP BY CUBE ([Group], [Name])

 

— WITH CUBE Equivalent

SELECT      [Group], [Name], SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.Sales

GROUP BY GROUPING SETS (([Group], [Name]), ([Group]), ([Name]), ())

Result Set (2nd):

Group                          Name                           Total Sales

—————————— —————————— —————————————

North America                  Northwest                      123237.00

South America                  Northwest                      37534.00

NULL                           Northwest                      160771.00

North America                  Southwest                      164232.00

South America                  Southwest                      39667.00

NULL                           Southwest                      203899.00

NULL                           NULL                           364670.00

North America                  NULL                           287469.00

South America                  NULL                           77201.00

 

(9 row(s) affected)

 

Let’s take another example, if you need to generate a pivot table based on sales data, you will need to group data by multiple sets:

Product

Year

 

 

2008

1009

2010

2011

Total

Product 1

GROUP BY (Product, Year)

GROUP BY (Product)

Product 2

Product 3

ALL

GROUP BY (Year)

GROUP BY ()

To generate this pivot, we need to group results by four different sets, This can be written using T-SQL as below:

— Equivalent to GROUP BY CUBE

SELECT      [Product], [Year], SUM(SalesYTD) AS 'Total Sales'

FROM        dbo.ProductSales

GROUP BY GROUPING SETS

(     ([Product], [Year]),

      ([Product]),

      ([Year]),

      ()

)

Hope This Helps!

Vishal

SQL Server – TSQL – GROUP BY WITH ROLLUP

07.07.2011 16 comments

GROUP BY groups the result set into summary rows by provided columns. For example, consider below data which contains sales figures by region.

Group                Name                 SalesYTD

——————   ——————-  —————

North America        Northwest            123237.00

North America        Northwest             37534.00

North America        Northwest             48003.00

North America        Southwest            164232.00

North America        Southeast             39667.00

North America        Southeast            105810.00

Europe               France                74569.00

Europe               Germany               59456.00

Pacific              Australia             93403.00
Europe               United Kingdom        78327.00

 

This data can be summarized using a GROUP BY clause as below:

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'

FROM        #TempTable

GROUP BY    [Group], [Name]

ORDER BY    [Group], [Name]

Result Set:

Group                Name                 Total Sales

——————   ——————   —————–

Europe               France                74569.00

Europe               Germany               59456.00

Europe               United Kingdom        78327.00

North America        Northwest            208774.00

North America        Southeast            145477.00

North America        Southwest            164232.00

Pacific              Australia             93403.00

 

(7 row(s) affected)

In the above result we can see that the data is summarized by [Group] and [Name].

 

WITH ROLLUP:

ROLLUP can be used to generate a subtotal rows and a grand total row for aggregate rows.

SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'

FROM        #TempTable

GROUP BY    [Group], [Name] WITH ROLLUP

Result Set:

Group                Name                 Total Sales

——————-  ——————-  ———————

Europe               France                74569.00

Europe               Germany               59456.00

Europe               United Kingdom        78327.00

Europe               NULL                 212352.00

North America        Northwest            208774.00

North America        Southeast            145477.00

North America        Southwest            164232.00

North America        NULL                 518483.00

Pacific              Australia             93403.00

Pacific              NULL                  93403.00

NULL                 NULL                 824238.00

 

(11 row(s) affected)

As we can see in the result set, 4 rows have been added, 3 for sub total of each [Group], and 1 row for grand total.

 

GROUPING():

GROUPING() function can be used to check whether the row is aggregated or not. It returns 1 for aggregated rows.

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      [Group], [Name], SUM([SalesYTD])  AS 'Total Sales',

            GROUPING([Group]) AS 'Aggregated',

            GROUPING([Name]) AS 'Aggregated'

FROM        #TempTable

GROUP BY    [Group], [Name] WITH ROLLUP

Result Set:

Group                Name                 Total Sales   Aggregated    Aggregated

——————-  ——————– ————- ————  ————

Europe               France                74569.00     0             0

Europe               Germany               59456.00     0             0

Europe               United Kingdom        78327.00     0             0

Europe               NULL                 212352.00     0             1

North America        Northwest            208774.00     0             0

North America        Southeast            145477.00     0             0

North America        Southwest            164232.00     0             0

North America        NULL                 518483.00     0             1

Pacific              Australia             93403.00     0             0

Pacific              NULL                  93403.00     0             1

NULL                 NULL                 824238.00     1             1

 

(11 row(s) affected)

 

Finally, we can use GROUPING() function to identify and replace the 'NULL' in aggregated rows to something meaningful. for example, 'ALL'!!!!.

SELECT      CASE GROUPING([Group])

                  WHEN 1 THEN 'ALL'

                  ELSE [Group] END AS 'Group',

            CASE GROUPING([Name])

                  WHEN 1 THEN 'ALL'

                  ELSE [Name] END AS 'Name',

            SUM([SalesYTD])  AS 'Total Sales'

FROM        #TempTable

GROUP BY    [Group], [Name] WITH ROLLUP

Result Set:

Group                Name                 Total Sales

——————   ——————-  ——————–

Europe               France                74569.00

Europe               Germany               59456.00

Europe               United Kingdom        78327.00

Europe               ALL                  212352.00

North America        Northwest            208774.00

North America        Southeast            145477.00

North America        Southwest            164232.00

North America        ALL                  518483.00

Pacific              Australia             93403.00

Pacific              ALL                   93403.00

ALL                  ALL                  824238.00

 

(11 row(s) affected)

Hope This Helps!

Vishal