Michael M. Michael M. - 3 months ago 20
SQL Question

Specific text to ROLLUP / Subtotals in Oracle

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

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 column, then the following is a more correct way to do it:

SELECT   REL_NR,
         CASE WHEN GROUPING(ERR_CODE) = 1 
            THEN 
               CASE WHEN REL_NR IS NULL 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;