CODEWITHSUNDEEP
sqlsql-serversql-server-2008
TimeIsNear
TimeIsNear

Reputation: 755

Alternative to the ROLLUP

I need to calculate subtotals in a separate line, but I cannot use ROLLUP or CUBE (because not allowed). I use SQL server 2008. There is an other way to get follow results? thanks in advance!

SELECT deptno, empno, SUM (sal) 2 FROM emp_test 3 GROUP BY ROLLUP (deptno, empno);

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

Upvotes: 1

Views: 6229

Answers (3)

Adam Wenger
Adam Wenger

Reputation: 17560

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
)

Actual query below:

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)

Upvotes: 2

Dibstar
Dibstar

Reputation: 2364

If you have SQL Server 2008 you could consider Grouping sets instead?

For your case I think it would be:

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

But I only have 2005 so can't test it!

Upvotes: 1

Amritpal Singh
Amritpal Singh

Reputation: 1785

you rather write a procedure using Cursor to obtain similar result.

Upvotes: 0

Related Questions