Grouping sets: display subtotals in other specific

问题:

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

回答1:

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)

回答2:

In SqlServer (2005+) you can use the ROLLUP command to get that type of data

SQL 2005

SELECT DeptNo, EmpNo, SUM(SAL),
   FROM myTable
   GROUP BY DeptNo, EmpNo WITH ROLLUP

or in 2008

SELECT DeptNo, EmpNo, SUM(SAL),
   FROM myTable
   GROUP BY ROLLUP(DeptNo, EmpNo)

EDIT:

Ideally, this would use the ROLLUP, and then deal with the data parsing in the UI, or manipulate it in the business logic layer (or even the UI) to display the data as needed.

BUT, If you have to do this in the SQL Server, then you can build a TEMP table in a function or stored procedure to give you the data that you need, but it will require multiple passes at the data.

回答3:

Use an outer select to reformat the columns:

select deptno, empno, 
    CASE WHEN empno is null THEN "" ELSE "sum_salary" END, 
    CASE WHEN empno is null THEN "sum_salary" ELSE "" END
from ( SELECT deptno,empno,SUM(sal) as sum_salary  FROM emp_test 3   
       GROUP BY GROUPING SETS  ((DeptNo,Empno),(DeptNo)) );