GROUP BY ALL simplifies the common case, but it also works with advanced grouping operations. Let’s look at patterns where it really pays off.
Complex analytical queries with many dimensions:
-- Before 23ai: manually listing 6 non-aggregated columns in GROUP BY
SELECT
region, country, product_line, product_family, channel, quarter,
SUM(revenue) AS total_revenue,
SUM(units_sold) AS total_units,
AVG(unit_price) AS avg_price
FROM sales_facts
GROUP BY region, country, product_line, product_family, channel, quarter;
-- With GROUP BY ALL:
SELECT
region, country, product_line, product_family, channel, quarter,
SUM(revenue), SUM(units_sold), AVG(unit_price)
FROM sales_facts
GROUP BY ALL;Adding a new dimension (say, sales_rep_id) to the analysis: before 23ai, you must add it to both SELECT and GROUP BY. With GROUP BY ALL, you only add it to SELECT.
GROUP BY ROLLUP ALL — subtotals without listing columns:
SELECT region, country, SUM(revenue) AS total
FROM sales_facts
GROUP BY ROLLUP ALL;
-- Equivalent to: GROUP BY ROLLUP(region, country)
-- Produces: region+country subtotals, region subtotals, grand totalGROUP BY CUBE ALL — cross-dimensional analysis:
SELECT channel, quarter, SUM(revenue)
FROM sales_facts
GROUP BY CUBE ALL;
-- All combinations: channel+quarter, channel only, quarter only, grand totalInteraction with window functions:
GROUP BY ALL does not apply to window functions — they’re not aggregate expressions in the GROUP BY sense. Window functions work the same as always:
SELECT region, product_line, SUM(revenue) AS group_total,
SUM(SUM(revenue)) OVER (PARTITION BY region) AS region_total
FROM sales_facts
GROUP BY ALL;
-- GROUP BY ALL includes: region, product_line
-- Window function operates on the grouped resultsGROUP BY ALL is at its best in analytical queries with 4+ dimensions where GROUP BY maintenance becomes error-prone.
