> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dynamosql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Grouping and Aggregation

> GROUP BY, HAVING, and aggregate functions.

## GROUP BY

Group rows by one or more columns and apply aggregate functions to each group:

```sql theme={null}
SELECT status, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY status
```

Multiple grouping columns:

```sql theme={null}
SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY customer_id, status
```

Positional ordinals are also supported. `GROUP BY 1, 2` groups by the first and second
select-list expressions:

```sql theme={null}
SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY 1, 2
```

Grouping expressions are also supported directly:

```sql theme={null}
SELECT region || ':' || status AS region_status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY region || ':' || status
```

## HAVING

Filter groups after aggregation. You can use both aggregate and non-aggregate predicates:

```sql theme={null}
-- Keep only customers with more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > 5

-- Combine aggregate and non-aggregate conditions
SELECT customer_id, SUM(total) AS total_spent
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 1000 AND customer_id IS NOT NULL
```

## Aggregate Functions

| Function      | Description                    |
| ------------- | ------------------------------ |
| `COUNT(*)`    | Count all rows in the group    |
| `COUNT(col)`  | Count non-NULL values in `col` |
| `SUM(col)`    | Sum of non-NULL values         |
| `AVG(col)`    | Average of non-NULL values     |
| `MIN(col)`    | Minimum value                  |
| `MAX(col)`    | Maximum value                  |
| `STDDEV(col)` | Population standard deviation  |

```sql theme={null}
SELECT
  category,
  COUNT(*) AS total_products,
  COUNT(description) AS with_description,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  STDDEV(price) AS price_stddev
FROM myschema.products
GROUP BY category
```

## DISTINCT Aggregates

Deduplicate values before aggregating:

```sql theme={null}
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM myschema.orders

SELECT SUM(DISTINCT total) AS sum_unique_totals
FROM myschema.orders
```

## Using Aliases in ORDER BY

Aliases defined in the `SELECT` list are available in `ORDER BY`. They cannot be used in `GROUP BY` or `HAVING` — repeat the expression there instead.

```sql theme={null}
SELECT customer_id, SUM(total) AS lifetime_value
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 500
ORDER BY lifetime_value   -- alias is valid here
```

## GROUPING SETS

`GROUPING SETS` lets you compute aggregates for multiple grouping combinations in a single query. Columns not part of a given grouping set are returned as `NULL`:

```sql theme={null}
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY GROUPING SETS((region), (status))
```

## ROLLUP

`ROLLUP` creates subtotals that roll up from the most detailed level to a grand total. `ROLLUP(a, b)` is equivalent to `GROUPING SETS((a, b), (a), ())`:

```sql theme={null}
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY ROLLUP(region, status)
```

## CUBE

`CUBE` generates subtotals for all possible combinations of the grouping columns. `CUBE(a, b)` is equivalent to `GROUPING SETS((a, b), (a), (b), ())`:

```sql theme={null}
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY CUBE(region, status)
```

## Not Supported

* Window functions (`ROW_NUMBER`, `RANK`, `SUM OVER`, etc.)