# GROUP BY

`GROUP BY` combines rows with equal grouping values and calculates aggregates independently for each group. The result usually contains one row per distinct grouping key.

All `NULL` values in a grouping key belong to the same group. Text grouping uses the selected collation.

## Syntax

```text
SELECT key_expression, aggregate_function(value)
FROM source
[WHERE condition]
GROUP BY key_expression [, ...]
[HAVING aggregate_condition]
```

Grouping can use expressions or result-column positions. Explicit expressions are easier to maintain when the projection changes. `GROUP BY` does not sort the final result; use `ORDER BY`.

`ROLLUP`, `CUBE` and `GROUPING SETS` are not supported.

## Examples

### Totals per region

```sql
CREATE TABLE sales (region TEXT, amount INTEGER);
INSERT INTO sales VALUES ('East', 10), ('East', 20), ('West', 5);
```

```sql
SELECT region, count(*) AS orders, sum(amount) AS total
FROM sales
GROUP BY region
ORDER BY region
FORMAT csv;
```

```text
"East",2,30
"West",1,5
```

### Grouping an expression

```sql
CREATE TABLE visits (country TEXT);
INSERT INTO visits VALUES ('GB'), ('gb'), ('FR'), (NULL), (NULL);
```

```sql
SELECT lower(country) AS country, count(*) AS visits
FROM visits
GROUP BY lower(country)
ORDER BY country NULLS LAST
FORMAT csv;
```

```text
"fr",1
"gb",2
\N,2
```

Without `GROUP BY`, an aggregate query treats the whole input as one group, including empty input. A grouped query with no admitted rows returns no groups.

See [aggregate functions](https://coordinatordb.com/docs/reference/functions/aggregate-functions) and [`HAVING`](https://coordinatordb.com/docs/reference/statements/select/having).