Sql – Grouping sets: display subtotals in other specific column

sqlsql serversql-server-2005

it is possible to display subtotals in other specific column?

SELECT deptno,empno,SUM(sal) as sum_salary
FROM emp_test 3 
GROUP BY GROUPING SETS
((DeptNo,Empno),(DeptNo),())

Actually I get this result:

DEPTNO      EMPNO   SUM(SAL)
10       7782      20000
10       7839      10000
10                 30000
20       7566       5950
20       7788       6000
20       7902       6000
20                 17950
30       7698      20000
30       7699      20000
30                 40000
                   87950

How can I get that? :

DEPTNO      EMPNO   SUM(SAL)  Subtotals

10       7782      20000
10       7839      10000
10                            30000
20       7566       5950
20       7788       6000
20       7902       6000
20                            17950
30       7698      20000
30       7699      20000
30                            40000

Note: I can't use ROLLUP or CUBE.

Thanks

Best Answer

I agree with Jamie you may want the subtotals visually handled in a different layer, but what you might want to try is using the GROUPING() function on the column. This function returns 1 if it is part of the GROUPING SETS subtotals, and 0 if it is a regular column. http://technet.microsoft.com/en-us/library/ms178544(SQL.90).aspx

I included the sample data I tested with. Remove the first WITH emp_test AS () when you use the select statement.

My Test Data:

WITH emp_test AS
(
   SELECT 10 AS DEPTNO, 7782 AS EMPNO, 20000 AS sal
   UNION ALL SELECT 10, 7839, 10000
   UNION ALL SELECT 20, 7566, 5950
   UNION ALL SELECT 20, 7788, 6000
)

Answer to get Subtotals on separate column:

SELECT deptno, empno
   , CASE
      WHEN GROUPING(empNo) = 1 THEN null
      ELSE SUM(sal)
     END as sum_salary
   , CASE
      WHEN GROUPING(empno) = 1 THEN SUM(sal)
      ELSE NULL
     END AS SubTotal
FROM emp_test
GROUP BY GROUPING SETS (DeptNo, Empno), (DeptNo)
Related Topic