2 ms·
I've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly: `group by cube`/`group by coalesce` aren't special advanced featur
by OJFord 4y ago
I've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly:
`group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately for every function.
- remram 4y agoYour suggestion is incorrect. CUBE is not part of the expression.
- OJFord 4y agoWell, I did say I wasn't familiar with it, but it's correct for `coalesce` and I don't think `cube` is different: https://www.postgresql.org/docs/current/cube.html https://www.postgresql.org/docs/current/cube.html In the context of `group by` it's treated as grouping sets, but that's not its only use. (Though that does seem to be special cased in terms of parsing, since afaict - I can't find the full query BNF on mobile - `grouping sets` is not optional.)
- remram 4y agohttps://www.postgresql.org/docs/14/sql-select.html https://www.postgresql.org/docs/14/sql-select.html GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] grouping_element can be one of: ( ) expression ( expression [, ...] ) ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) CUBE ( { expression | ( expression [, ...] ) } [, ...] ) GROUPING SETS ( grouping_element [, ...] ) You're right about COALESCE of course.
- zweifuss 4y agoCube gets all possible combinations of grouping sets. It´s like showing all subtotals in a pivot table. That´s different than just grouping on the lowest level without totals.