Wednesday, July 29, 2015

GROUPING ID in SQL Server

/*
UNDERSTANDING THE GROUPING ID IN SQL SERVER.....
IS A FUNCTION THAT COMPUTES THE LEVEL OF GROUPING. GROUPING_ID CAN BE USED ONLY IN THE SELECT <SELECT> LIST, HAVING, OR ORDER BY CLAUSES
WHEN GROUP BY IS SPECIFIED.
*/
DECLARE @T TABLE(REGION VARCHAR(100), NAME VARCHAR(100), AMOUNT DECIMAL(12,2))
INSERT INTO @T
SELECT 'REGION1','NAME1',2000 UNION ALL
SELECT 'REGION1','NAME2',100  UNION ALL
SELECT 'REGION1','NAME3',500  UNION ALL
SELECT 'REGION1','NAME3',3400 UNION ALL
SELECT 'REGION2','NAME1',3233 UNION ALL
SELECT 'REGION2','NAME2',5000 UNION ALL
SELECT 'REGION2','NAME2',5344 UNION ALL
SELECT 'REGION3','NAME1',1200 UNION ALL
SELECT 'REGION3','NAME2',900  UNION ALL
SELECT 'REGION4','NAME1',2540

SELECT * FROM @T

SELECT REGION,NAME,SUM(AMOUNT) AS AMOUNT,GROUPING_ID(REGION) AS [GROUPING] FROM @T
GROUP BY REGION, NAME
WITH ROLLUP


/* SEE THIS LINK FOR EXAMPLE HTTPS://MSDN.MICROSOFT.COM/EN-US/LIBRARY/BB510624.ASPX */

No comments:

Post a Comment