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 theGROUPING SETS
subtotals, and 0 if it is a regular column. http://technet.microsoft.com/en-us/library/ms178544(SQL.90).aspxI included the sample data I tested with. Remove the first
WITH emp_test AS ()
when you use the select statement.My Test Data:
Answer to get Subtotals on separate column: