Archive

Posts Tagged ‘sub total’

SQL Server – TSQL – GROUP BY CUBE()

08.07.2011 1 comment

CUBE() produces the result set by generating all combinations of columns specified in GROUP BY CUBE(). As with GROUP BY ROLLUP() it adds sub-total and grand-total rows as well.

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)

When we apply GROUP BY CUBE([Group], [Name]) to this data, it will generate:

1. Combination of all columns specified:

([Group]) – 2 rows (North America, South America)

([Name]) – 2 rows (Northwest, Southwest)

2. Sub-total rows:

Sub-total for Northwest, Southwest  – 2 rows

Sub-total for North America, South America – 2 rows

3. Grand-total row: 1 row

Thereby, the result set will contain 9 rows as below:

SELECT      COALESCE([Group], 'ALL') AS 'Group',

            COALESCE([Name],'ALL') AS 'Name',

            SUM([SalesYTD]) AS 'Total Sales'

FROM        dbo.Sales

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

Result Set:

Group                Name                 Total Sales

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

North America        Northwest            123237.00

South America        Northwest             37534.00

ALL                  Northwest            160771.00

North America        Southwest            164232.00

South America        Southwest             39667.00

ALL                  Southwest            203899.00

ALL                  ALL                  364670.00

North America        ALL                  287469.00

South America        ALL                   77201.00

 

(9 row(s) affected)

 

If GROUP BY ROLLUP() is used here, it will generate 7 rows, removing 2 sub-total rows for [Group] column from the result set:

SELECT      COALESCE([Group], 'ALL') AS 'Group',

            COALESCE([Name],'ALL') AS 'Name',

            SUM([SalesYTD]) AS 'Total Sales'

FROM        dbo.Sales

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

Result Set:

Group                Name                 Total Sales

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

North America        Northwest            123237.00

North America        Southwest            164232.00

North America        ALL                  287469.00

South America        Northwest            37534.00

South America        Southwest            39667.00

South America        ALL                  77201.00

ALL                  ALL                  364670.00

 

(7 row(s) affected)

 

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