GROUP BY

GROUP BY groups rows that have the same values for specified expressions when computing aggregate functions. Grouping can be done by a column or any general expression.

Syntax

SELECT ... 
  FROM ... 
  [...] 
  GROUP BY {col_alias | expr}  
           [, {col_alias | expr} [, ...]]  
  [...]

Parameters

col_alias: The column alias as defined in the SELECT list of the query block.

---

expr: Any expression that can be applied to objects in the current scope.

Usage Notes

• Expressions used in a GROUP BY clause must be referenced by name only.
• Each alias may only be assigned once per query.

Example 1 | GROUP BY a Single Column

WITH
t1 AS (
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 1 AS col1, 3 AS col2
  UNION ALL 
  SELECT 2 AS col1, 4 AS col2
  UNION ALL 
  SELECT 2 AS col1, 5 AS col2
  UNION ALL 
  SELECT 3 AS col1, 6 AS col2
)
SELECT col1, SUM(col2) AS sum_col2
  FROM t1
  GROUP BY col1;

+------+----------+
| COL1 | SUM_COL2 |
+------+----------+
| 1    | 5        |
| 2    | 9        |
| 3    | 6        |
+------+----------+

Example 2 | GROUP BY Multiple Columns

WITH
t1 AS (
  SELECT 1 AS col1, 2 AS col2, 1 AS col3
  UNION ALL
  SELECT 1 AS col1, 3 AS col2, 2 AS col3 
  UNION ALL 
  SELECT 2 AS col1, 4 AS col2, 1 AS col3
  UNION ALL 
  SELECT 2 AS col1, 5 AS col2, 2 AS col3
  UNION ALL 
  SELECT 2 AS col1, 6 AS col2, 1 AS col3
)
SELECT col1, col3, SUM(col2) AS sum_col2
  FROM t1
  GROUP BY col3, col1;

+------+------+----------+
| COL1 | COL3 | SUM_COL2 |
+------+------+----------+
| 1    | 2    | 3        |
| 2    | 1    | 10       |
| 1    | 1    | 2        |
| 2    | 2    | 5        |
+------+------+----------+