How do GROUP BY and HAVING work?

Question

Accepted Answer

**`GROUP BY`** groups rows that share a value, so aggregate functions compute per group. **`HAVING`** filters those groups (like `WHERE`, but for groups/aggregates). Together they're the core of summarizing and analyzing data by category.

## GROUP BY — aggregate per group

```sql
SELECT country, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM users
GROUP BY country;
-- one result row PER country, with that country's count and average age
```

`GROUP BY country` collapses all rows of each country into one group, and aggregate functions (`COUNT`, `AVG`) compute a value for each group. You can group by multiple columns:

```sql
GROUP BY country, city          -- a group per unique (country, city) combination
```

## HAVING — filter the groups

```sql
-- show only countries with MORE THAN 100 users
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;          -- filter on the AGGREGATE
```

`HAVING` filters *after* grouping, on aggregate values — something `WHERE` can't do (WHERE filters individual rows before grouping).

## WHERE vs HAVING — a key distinction

```sql
SELECT country, COUNT(*) AS cnt
FROM users
WHERE active = true             -- WHERE: filter ROWS first (before grouping)
GROUP BY country
HAVING COUNT(*) > 100;          -- HAVING: filter GROUPS after (on aggregates)
```

```text
WHERE  → filters individual ROWS, runs BEFORE grouping, can't use aggregates
HAVING → filters GROUPS, runs AFTER grouping, used WITH aggregates
→ Use WHERE to filter rows, HAVING to filter aggregated groups. (Filter with WHERE
  first when possible — it's more efficient, reducing rows before grouping.)
```

## The execution order

```text
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
(WHERE before grouping, HAVING after — which is why their roles differ)
```

## Why it matters

`GROUP BY` and `HAVING` are core to **analytical SQL** — summarizing and analyzing data by category is a fundamental, constant need (counts per region, sales per product, averages per group), so understanding them is essential. `GROUP BY` (grouping rows so aggregates compute per group) is the foundation of these summary queries, and understanding how it collapses rows into groups for aggregation is necessary for any reporting or analytics.

The **`WHERE` vs `HAVING` distinction** is particularly important and commonly confused: `WHERE` filters individual rows *before* grouping (and can't use aggregates), while `HAVING` filters groups *after* grouping (on aggregate values) — using the wrong one causes errors or wrong results, and understanding when each applies (WHERE for row-level filtering, HAVING for aggregate-level filtering of groups) is a key skill.

Knowing to filter with WHERE first when possible (more efficient — reducing rows before grouping) is a useful optimization.

The connection to **execution order** (WHERE before grouping, HAVING after) explains why their roles differ.

Since grouping and aggregate filtering are ubiquitous in reporting and data analysis, and since the WHERE-vs-HAVING distinction is both important for correctness and commonly misunderstood, mastering `GROUP BY` and `HAVING` — grouping for per-category aggregates and the two-level filtering model — is core, frequently-applied knowledge essential for analytical SQL and a common interview topic.