anil nain programmer sonipat

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

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

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

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