sql grouping set

CUBE, ROLLUP, and GROUPING SETS

ROLLUP

The ROLLUP keyword will inject an additional line, which will contain the overall average.

SELECT region, country, avg(production)
 FROM t_oil
WHERE  country IN ('USA', 'Canada', 'Iran', 'Oman')
GROUP BY ROLLUP (region, country);

CUBE

The resultant cube contains all possible combinations of groups. Compute every posible agregation: GROUP BY region, country + GROUP BY region + GROUP BY country + the overall average.Not depend on column position in Group by

  SELECT region, country, avg(production)
    FROM t_oil
   WHERE country IN ('USA', 'Canada', 'Iran', 'Oman')
   GROUP BY CUBE (region, country);

GROUPING SETS

You can explicitly list the aggregates you want:

  SELECT region, country, avg(production)
    FROM  t_oil
   WHERE country IN ('USA', 'Canada', 'Iran', 'Oman')
   GROUP BY GROUPING SETS ( (), region, country);

The hash-based version (MixedAggregate) is faster then GroupAggregate to and is favored by the optimizer if there is enough memory to keep the hash needed for MixedAggregatein memory.