On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, June 2, 2015

Roll-up without Subtotals


Rollup usually will show subtotals on every difference in the group. To show only a grand total, add 'grouping sets' and a '1' as shown below:

SELECT deptno 
     , job 
     , SUM(sal) 
  FROM emp 
 WHERE deptno <> 10                  -- narrow down this example 
   AND job IN ( 'MANAGER', 'CLERK' ) -- narrow down this example 
 GROUP BY grouping sets ( ( deptno, job ) -- One row for every distinct combination of these 
                          , 1             -- Grand total super-aggregate 
                        ); 

DEPTNO JOB SUM(SAL)
30 CLERK 950
20 CLERK 1900
20 MANAGER 2975
30 MANAGER 2850
8675

Or you can add 'Total' to it by decoding the 1st column. The Grouping value is 1 when it's a total.

SELECT DECODE(
              GROUPING(deptno) -- Decode the GROUPING value for DeptNo
             ,1                -- If it is 1,
             ,'Total'          -- Then print 'Total'
             ,deptno           -- otherwise, print the actual DeptNo
             ) AS dept         -- call this column 'Dept_Tot'
     , job
     , SUM(sal)
  FROM scott.emp
 WHERE deptno <> 10                  -- narrow down this example
   AND job IN ( 'MANAGER', 'CLERK' ) -- narrow down this example
 GROUP BY grouping sets ( ( deptno, job ) -- One row for every distinct combination of these
                          , 1             -- Grand total super-aggregate
                        );

DEPT JOB SUM(SAL)
30 CLERK 950
20 CLERK 1900
20 MANAGER 2975
30 MANAGER 2850
Total 8675

No comments:

Post a Comment