SQL GROUP BY ALL and ORDER BY ALL

GROUP BY ALL tells DuckDB to group by every expression in the SELECT list that isn't an aggregate. You write the columns once, and the GROUP BY can't fall out of step with them. ORDER BY ALL sorts by every column, left to right.

select category_id,
       count(*) as products,
       round(avg(price), 2) as avg_price
from products
group by all
order by all

That returns one row per category, sorted by category_id. The portable form lists the column again: group by category_id order by category_id.

When to use it

• Exploring. Adding or removing a grouping column means editing one line instead of two.
• Grouping by an expression, such as upper(status) or a CASE, where repeating it in GROUP BY invites a typo.
• Queries that only DuckDB will run. Code that has to run on PostgreSQL, MySQL, or SQL Server needs the columns listed.

What GROUP BY ALL picks

Every SELECT expression that contains no aggregate becomes a grouping key, columns from a join included:

select c.category_name,
       count(*) as products
from products as p
join categories as c on c.category_id = p.category_id
group by all
order by products desc, c.category_name

A window function can't be grouped on: a SELECT list with sum(amount) over () next to plain columns fails with Cannot group on a window clause.

ORDER BY ALL

ORDER BY ALL sorts by the first column, then the second, and so on. ORDER BY ALL DESC reverses every column. It doesn't know which column you care about, so "the states with the most customers" still needs the sort spelled out:

select state,
       count(*) as customers
from customers
group by all
order by customers desc, state

With order by all instead, PA, which has the most customers (6), would sit fifth, between OR and TX.

What can go wrong

• Adding a column changes the grain. With an explicit GROUP BY, a new SELECT column that isn't grouped is an error. With GROUP BY ALL it silently becomes another key. Below, adding discontinued to look at it splits category 8 into two rows, and adding product_name would turn 8 rows into 24 rows of 1.
• ORDER BY ALL isn't "order by the metric". It sorts by the leftmost column first.
• SQL Server's GROUP BY ALL is something else. It's a deprecated form, followed by a column list, that keeps groups the WHERE clause filtered out, with NULL aggregates.

select category_id,
       discontinued,
       count(*) as products
from products
group by all
order by all

How other databases differ