zOs/SQL/EXGROUP

            -- example group by rollup/cube/grouping sets
            -- ein klassische group by
select tsName, count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
    group by tsName
;
            -- eine total Zeile waere doch schoen
            -- das braucht aber DB2 V11 Kompatibilitaet
set current application compatibility 'V11R1';
select tsName, count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
    group by rollup (tsName)
;
            -- das geht auch mehrstufig mit ZwischenTotalen
select left(tsName, 3), tsName, partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by rollup ( left(tsName, 3), tsName, partition )
;
            -- mit ORDER kriegen wir die Zwischentotale
            --      direkt hinter die summierten Daten
select left(tsName, 3), tsName, partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by rollup ( left(tsName, 3), tsName, partition )
    order by left(tsName, 3)
           , value(tsName, x'ff')
           , value(partition, 9999)
;
            -- mit ORDER kriegen wir die Zwischentotale
            --      oder auch vor die summierten Daten
select left(tsName, 3), tsName, partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by rollup ( left(tsName, 3), tsName, partition )
    order by value(left(tsName, 3), '')
           , value(tsName, '')
           , value(partition, -9999)
;
            -- mit rollup kriegten wir 3+1 =4 Total Kombinationen
            -- mit cube   aber         2**3=8 Total Kombinationen
select left(tsName, 3), tsName, partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by cube ( left(tsName, 3), tsName, partition )
;
            -- wir können die Kombinationen aber auch explizit schreiben
select left(tsName, 3), partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by grouping sets ( (left(tsName, 3)), (partition) )
;
            -- ein () liefert noch das grand total
select left(tsName, 3), partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by grouping sets ( (left(tsName, 3)), (partition), () )
;
            -- group by expressions, rollup, cube, und grouping sets
            --    können kombiniert und verschachtelt werden
select dbName, left(tsName, 3), tsName, partition
      , count(*) cnt, sum(spaceF) spc
    from sysibm.sysTablePart
    where dbName = 'QZ01A1P' and tsname < 'A02'
                             and partition < 5
    group by dbName, grouping sets(rollup(left(tsName, 3), tsName)
                                         , (partition ))
;
       -- noch ein cube
select left(tsName, 3), s.type, partition
       , count(*) cnt, sum(p.spaceF) spc
    from sysibm.sysTablePart p join sysibm.sysTablespace s
        on p.dbName = s.dbName and p.tsName = s.name
    where p.dbName = 'QZ01A1P' and p.tsname < 'A02'
                               and p.partition < 5
    group by cube( left(tsName, 3), partition, s.type)
;
       -- und verschiedene Kombinationen
with a (a, b, c) as
(
            select 'A', 'b',   1 from sysibm.sysDummy1
  union all select 'A', 'c',   2 from sysibm.sysDummy1
  union all select 'A', 'c',   3 from sysibm.sysDummy1
  union all select 'B', 'd',  14 from sysibm.sysDummy1
  union all select 'B', 'e',  15 from sysibm.sysDummy1
  union all select 'B', 'f',  16 from sysibm.sysDummy1
  union all select 'B', 'g',  17 from sysibm.sysDummy1
)
select           a, b, sum(c) sumC, '1 set: ((a, b))'
    from a
    group by grouping sets ((a, b))
union all select a, b, sum(c) sumC, 'sets : ((a, b), a, b, ())'
    from a
    group by grouping sets ((a, b), a, b, ())
union all select a, b, sum(c) sumC, 'rollU: (rollup (a, b))'
    from a
    group by grouping sets (rollup (a, b))
union all select a, b, sum(c) sumC, 'cube : (cube (a, b))'
    from a
    group by grouping sets (cube (a, b))