When I translated my tip about making sums and totals without the use of GROUP BY it came into my mind that DB2 11 features a new solution which is much simpler to use than the one I described in Week 39/2015. To use the new solution your DB2 subsystems must not only be on version 11, but also be in so-called New Function Mode (NFM). Therefore you might have to wait a while yet and use the approach described in week 39/2015 and then wait with excitement until you can use this tip.

Solving the same challenge as in the example from week 39/2015 the same result (approximately) can be acheived using the following SQL statement:

SELECT dbname, SUM(colcount), COUNT(*)
FROM sysibm.systables
WHERE creator = 'SYSIBM'
  and type = 'T'
GROUP BY ROLLUP(dbname)
ORDER BY dbname

First and foremost this statement is much easier to read and understand and compared to a traditional GROUP BY the only difference is to put a ROLLUP() around the columns you want to make totals on. In this example there is only one column and then DB2 just adds a single row at the bottom of the result set containing a total for each of the aggregated columns. Compared to the old solution you do not need the two columns with the rolling sum and the total is placed beneath the column it belongs to. All in all ROLLUP is a huge improvement and the correct way of making totals.

If you specify ROLLUP on more than one column there will be a sub-total for each time there is a break on the column to the far right, the next far right and so on. In the above example you can try this by removing the type = 'T' predicate and add type to the SELECT list and change ROLLUP to ROLLUP(dbname, type). Now you will get sub-totals within each database for each type and still keep the total. Remember to try it with ROLLUP(type, dbname) just to see the difference. The two different results can be produced as one result by using CUBE instead of ROLLUP. CUBE and ROLLUP produces the same result as long as only one column is specified in the parenthesis, but it just gets wilder and wilder for each column you add when using CUBE.

You might experience that the DB2 system where you try to conduct your ROLLUP experiment is in NFM, but it does not work anyway. The reason is that somebody has chosen to disable all the new fancy SQL that comes with V11. Fortunately you can circumvent this barrier by executing a SET CURRENT APPLICATION COMPATIBILITY = 'V11R1' just before you execute your ROLLUP or CUBE SQL statement.