Skip to content
Daniël van Eeden edited this page Mar 13, 2024 · 5 revisions

SQL standard defines the following syntax for GROUP BY clause:

GROUP BY [ALL | DISTINCT] expr ["," ...]

There's a considerable variation is dialects:

BigQuery:

GROUP BY { expr ["," ...] | ROLLUP { expr ["," ...] } }

DB2 & DB2i:

GROUP BY { expr | GROUPING SETS grouping_sets | super_groups } ["," ...]

grouping_sets:
  "(" { expr | super_groups | grouping_sets } ["," ...] ")"

super_groups:
  [ROLLUP | CUBE] "(" expr ["," ...] ")"

Hive:

GROUP BY expr ["," ...] [GROUPING SETS "(" expr ["," ...] ")"] [WITH ROLLUP | WITH CUBE]

MariaDB:

GROUP BY expr [ASC | DESC] ["," ...] [WITH ROLLUP]

MySQL:

GROUP BY expr ["," ...] [WITH ROLLUP]

N1QL additionally supports LETTING clause (which can follow or replace GROUP BY):

GROUP BY expr ["," ...]

LETTING alias "=" expr ["," ...]

PL/SQL:

GROUP BY {expr | rollup_cube | grouping | "()"} ["," ...]

rollup_cube:
  [ROLLUP | CUBE] "(" expr ["," ...] ")"

grouping_sets:
  GROUPING SETS "(" {rollup_cube | expr} ["," ...] ")"

PostgreSQL:

GROUP BY [ ALL | DISTINCT ] expr ["," ...]

Redshift:

GROUP BY expr ["," ...]

SingleStoreDB:

GROUP BY {expr ["," ...] | extended_grouping_expr}

extended_grouping_expr:
  {CUBE | ROLLUP} "(" expr ["," ...] ")"

Snowflake:

GROUP BY {expr ["," ...] | extended_grouping_expr}

extended_grouping_expr:
  {CUBE | ROLLUP | GROUPING SETS} "(" expr ["," ...] ")"

Spark:

GROUP BY expr ["," ...] [WITH ROLLUP | WITH CUBE]

GROUP BY {expr | {ROLLUP | CUBE | GROUPING SETS} "(" expr ["," ...] ")"} ["," ...]

SQLite:

GROUP BY expr ["," ...]

TiDB:

GROUP BY expr ["," ...] [WITH ROLLUP]

Transact-SQL:

GROUP BY {expr | {ROLLUP group_expr | CUBE group_expr | GROUPING SETS grouping_set | "()" } ["," ...]

GROUP BY [ALL] expr ["," ...] [WITH CUBE | WITH ROLLUP]

GROUP BY expr [WITH "(" DISTRIBUTED_AGG ")"] ["," ...]

group_expr:
    expr
  | "(" expr ["," ...] ")"

grouping_set:
    group_expr
  | ROLLUP "(" group_expr ["," ...] ")"
  | CUBE "(" group_expr ["," ...] ")"

Trino:

GROUP BY [ALL | DISTINCT] {grp_set | rollup_cube | grouping_sets} ["," ...]

rollup_cube:
  [ROLLUP | CUBE] "(" expr ["," ...] ")"

grouping_sets:
  GROUPING SETS "(" grp_set ["," ...] ")"

grp_set:
    expr
  | "(" expr ["," ...] ")"
Clone this wiki locally