As a developer you should know about GROUP BY GROUPING SETS. This is typically used when working with dimensions in a data warehouse. Data warehouse or not, GROUPING SETS is one of those tools you need in your toolbox.

GROUP BY GROUPING SETS is a extension of the GROUP BY clause. GROUPING SETS gives us the ability to express multiple group-by clauses in a single statement.

GROUP BY GROUPING SETS(a,b,c,())
is equivalent to
GROUP BY a
UNION ALL
GROUP BY b
UNION ALL
GROUP BY c
UNION ALL
GROUP BY ()

GROUP BY( ) ?

When we want the grand total. We can express this in three different ways using GROUP BY expressions.

select count(*) from emp;
select count(*) from emp group by();
select count(*) from emp group by grouping sets(());

By implicit GROUP BY( ) will be added to the first statement. This is also the case when using the HAVING clause.

Let’s create a query that displays the total number of employment made per year, the total number of employment made per year-month and finally the grand total of all employments.

In this example we have a table emp, which contains all employees and the date when each employee was hired.

select
    count(*),
    extract(year from hiredate) year,
    null yearmonth
from emp
group by extract(year from hiredate) --by year
union all
select
    count(*),
    null,
    to_char(hiredate, 'YYYY-MM')
from emp group by to_char(hiredate, 'YYYY-MM') --by yearmonth
union all
select count(*),null,null  --grand total
from emp;

Two big drawbacks here.

  1. Readability, overhead code
  2. Performance, Oracle will do three scans of the emp table to get the result

When using GROUPING SETS we can specify multiple groupings of data in one query.

select
    count(*) employment,
    extract(year from hiredate) year,
    to_char(hiredate, 'YYYY-MM') yearmonth
from emp
group by
    grouping sets (
      extract(year from hiredate),to_char(hiredate, 'YYYY-MM'), ( )
      )

I think this syntax is much clearer than the UNION ALL solution. Also Oracle only needs to scan the emp table once.

Over & Out