Here's one of the most useful SQL keywords you've never heard of:


GROUP BY CUBE

Think of GROUP BY CUBE as the ultimate grouping keyword. It essentially contains GROUP BY ROLLUP and GROUP BY GROUPING SETS inside of it.

How does it work?

Take our example dataset: the Citibike trips dataset. This data contains Citibike trips taken by riders in NYC.

Let's say you're grouping by three different columns: year, month, and station name. You're doing a COUNT() of trips taken.

What are all the possible groupings we could come up with?
1. Total trips overall
2. Trips from each station
3. Trips in each month
4. Trips in each year
5. Trips in each month + each station
6. Trips in each year + each station
7. Trips in each year + each month
8. Trips in each year + each month + each station

GROUP BY CUBE will perform all of these grouping combinations for you automatically.

Usage is super simple. All you need to do is type GROUP BY CUBE along with the names of the columns you'd like to group by.