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