Grouping Sets

Explore how to efficiently compute aggregates for multiple groupings in a single SQL query using grouping sets, rollup, and cube clauses. This lesson helps you understand their syntax and practical applications, enabling hierarchical and multidimensional data summarization.

We'll cover the following...

The rollup clause
The cube clause
Try it yourself

There is a restriction that comes with using classic aggregates—we can only run them through a single group definition at a time. In some cases, we want to be able to compute aggregates for several groups in parallel. For those cases, SQL provides the grouping sets feature.

In the Formula One competition, points are given to drivers and then used to compute both the driver’s champion and the constructor’s champion points. Can we compute those two sums over the same points in a single query? Yes, of course, we can:

PostgreSQL

\set season 'date ''1978-01-01'''
    select drivers.surname as driver,
           constructors.name as constructor,
           sum(points) as points
      
      from results
           join races using(raceid)
           join drivers using(driverid)
           join constructors using(constructorid)
   
   where date >= :season
     and date <  :season + interval '1 year'
  
  group by grouping sets((drivers.surname),
                         (constructors.name))
    having sum(points) > 20
  
  order by constructors.name is not null,
           drivers.surname is not null,
           points desc;