SUPER AGGREGATE FUNCTIONS,GROUPING AND COMPUTE BY

• super aggregates means aggregates of aggregates
• two types super aggregates 

                   1.cube 
                   2.rollup

syntax:      
                          SELECT column_list
                           FROM table_list
                           WHERE search_criteria
                           [GROUP BY [ALL] non_aggregate_expression(s)
                           [WITH {ROLLUP | CUBE} ]]

• single column group by rollup and cube get same output rollup

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO WITH CUBE

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO WITH ROLLUP

SELECT ISNULL(DEPTNO,0),SUM(SAL) FROM EMP GROUP BY DEPTNO WITH CUBE

• mutiple columns group by rollup and cube get different o/p.

• multiple columns group by with rollup and cube

     SELECT DEPTNO,JOB,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO,JOB                WITH ROLLUP

     SELECT DEPTNO,JOB,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO,JOB                WITH CUBE

grouping function:

• By using grouping function to replace null vaues in rollup and cube

                  select * from emp

rollup: 

 SELECT 

       case GROUPING(DEPTNO)
       WHEN 1 THEN 'DEPT_TOTAL'
       ELSE CAST(DEPTNO AS VARCHAR(10))
       END AS DEPTNO,
       case GROUPING(JOB)
       WHEN 1 THEN 'JOB_TOTAL'
       ELSE JOB
       END AS JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB WITH ROLLUP

cube:

 SELECT 

       case GROUPING(DEPTNO)
       WHEN 1 THEN 'DEPT_TOTAL'
       ELSE CAST(DEPTNO AS VARCHAR(10))
       END AS DEPTNO,
       case GROUPING(JOB)
       WHEN 1 THEN 'JOB_TOTAL'
       ELSE JOB
       END AS JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB WITH CUBE

compute by:

•     Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.
• This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ROLLUP instead.

SELECT * FROM EMP1

SELECT * FROM EMP ORDER BY DEPTNO,JOB COMPUTE COUNT(EMPNO),COUNT(SAL),MAX(SAL),MIN(SAL) BY DEPTNO


SELECT * FROM EMP ORDER BY JOB,DEPTNO

SELECT * FROM EMP COMPUTE SUM(SAL)

SELECT * FROM EMP ORDER BY DEPTNO COMPUTE SUM(SAL) BY DEPTNO

SELECT * FROM EMP ORDER BY JOB COMPUTE COUNT(SAL),MAX(SAL),MIN(SAL) BY JOB

SELECT * FROM EMP ORDER BY JOB,DEPTNO COMPUTE COUNT(SAL),MAX(SAL),MIN(SAL) BY JOB,DEPTNO