Saturday, 9 March 2013

how to add subTotal, grandTotal in sql query

DECLARE @t TABLE (RowNum INT IDENTITY(1,1),Grp CHAR(1), Val INT)

 INSERT INTO @t
 SELECT NULL, 1 UNION ALL
 SELECT 'A', 2 UNION ALL
 SELECT 'A', 3 UNION ALL
 SELECT 'A', 3 UNION ALL
 SELECT 'B', 4 UNION ALL
 SELECT 'A', 3 UNION ALL
 SELECT 'B', 5

 SELECT d.Grp,d.GroupTotal
   FROM (
         SELECT RowNum,
                Grp = CASE
                        WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 0 THEN Grp
                        WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 1 THEN 'Sub Total ' + ISNULL(grp,'NULL')
                        ELSE 'Grand Total'
                      END,
                GroupTotal = SUM(Val)
           FROM @t
          GROUP BY Grp,Val,RowNum WITH ROLLUP
        ) d
  WHERE RowNum > 0 OR Grp LIKE '%Total%'


Grp                  GroupTotal
NULL                   1            
Sub Total N         1            
A                          2            
A                          3            
A                          3            
A                          3            
Sub Total A         11          
B                          4            
B                          5            
Sub Total B         9            
Grand Total        21           

No comments:

Post a Comment