zlacker

What’s group-by-all? Sounds like distinct?

>>aidos+(OP)
Normally the SELECT has a bunch of columns to group by and a bunch of columns that are aggregates. Then, in the GROUP BY clause, you have to list all the columns to group by. The query compiler knows which they are, and polices you, making sure you got it right. All the GROUP BY ALL does is say 'the compiler knows, there's no need to list them all'. Very convenient.

BigQuery supports GROUP BY ALL and it really cleans up lots of queries. E.g.

   SELECT foo, bar, SUM(baz)
   FROM x
   GROUP BY ALL <-- equiv to GROUP BY foo, bar

(eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)

>>aidos+(OP)
It's different from distinct. Distinct just eliminates duplicates but does not group entries.

Suppose...

  SELECT brand, model, revision, SUM(quantity)
   FROM stock
   GROUP BY brand, model, revision

This is not solved by using distinct as you would not get the correct count.

Group By All allows you to write it a bit more compact...

  SELECT brand, model, revision, SUM(quantity)
   FROM stock
   GROUP BY ALL

>>willva+j2
MySQL doesn't do this anymore; the ONLY_FULL_GROUP_BY mode became default in 5.7 (I think). You can still turn it off and get the old behavior, though.

>>geneze+w3
Gotcha. Thanks. That’s actually super useful! Looks like Postgres doesn’t implement it unfortunately.

I revert to “group by 1, 2, 3… “ when I’m just hacking about. Group by all would definitely be an improvement.