Specific text to ROLLUP / Subtotals in Oracle

Question

I have following Query:

  SELECT REL_NR, ERR_CODE, COUNT (ERR_CODE)
  FROM ZDL_ERR
  WHERE ERR_CODE > 0
  GROUP BY ROLLUP (REL_NR, ERR_CODE)
  ORDER BY REL_NR DESC, ERR_CODE;

with following Output:

196 1148   16
196 1180 2288
196 1181  177
196      2481

Is there a way to give a ROLLUP a text so it displays something like this:

196 1148   16
196 1180 2288
196 1181  177
Subtotal 2481

Answer 1

You could replace the null value you get that label in the second column:

SELECT   REL_NR, 
         COALESCE(TO_CHAR(ERR_CODE), 
           CASE WHEN REL_NR IS NULL THEN 'Grand Total'
                                    ELSE 'Sub Total'
           END) AS ERR_CODE,
         COUNT (ERR_CODE)
FROM     ZDL_ERR
WHERE    ERR_CODE > 0
GROUP BY ROLLUP (REL_NR, ERR_CODE)
ORDER BY REL_NR, ERR_CODE;

If you have null values in your ERR_CODE or REL_NR columns, then the following is a more correct way to do it:

SELECT   REL_NR,
         CASE GROUPING(ERR_CODE) 
             WHEN 1 THEN 
                 CASE GROUPING(REL_NR)
                     WHEN 1 THEN 'Grand Total'
                            ELSE 'Sub Total'
                 END
             ELSE TO_CHAR(ERR_CODE)
         END AS ERR_CODE,
         COUNT (ERR_CODE)
FROM     ZDL_ERR
WHERE    ERR_CODE > 0
GROUP BY ROLLUP (REL_NR, ERR_CODE)
ORDER BY REL_NR, ERR_CODE;