# GROUP BY, HAVING and FILTER — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/g-group

> Several metrics in one pass.

## Conditional aggregates

Every selected column must be grouped or aggregated. `HAVING` filters groups after aggregation. The SQL-standard `FILTER (WHERE ...)` clause (supported by PostgreSQL) computes conditional aggregates such as "hired since 2022" alongside totals in a single scan; elsewhere use `sum(CASE WHEN ... THEN 1 ELSE 0 END)`. `count(DISTINCT x)` counts unique values. Note that an inner join to departments drops employees without a department.

## Summaries that answer questions

GROUP BY, FILTER, ROLLUP and conditional aggregation produce most business reports.

![Three ideas: grouping with filters, subtotals, pivots.](assets/figures/sql-deep-dive/section-3-map.svg) — Figure 3.1 — Grouping, rollups and pivots.

## Department metrics with FILTER and HAVING, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Support (one person) is removed by HAVING; Nina has no department and is excluded by the inner join.

```sql
SELECT d.name AS dept,
       count(*) AS people,
       round(avg(e.salary)) AS avg_salary,
       count(*) FILTER (WHERE e.hired >= '2022-01-01') AS hired_since_2022,
       count(DISTINCT e.salary) AS distinct_salaries
FROM employees e JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
HAVING count(*) >= 2
ORDER BY people DESC;
```

Output:

```
    dept     | people | avg_salary | hired_since_2022 | distinct_salaries 
-------------+--------+------------+------------------+-------------------
 Engineering |      4 |     182500 |                1 |                 3
 Sales       |      3 |     111667 |                2 |                 3
(2 rows)
```

## Round only for presentation

Keep full precision in intermediate steps and round in the final SELECT.

**Quiz:** What does count(*) FILTER (WHERE hired >= '2022-01-01') compute?

- [x] The number of rows in each group hired since 2022
- [ ] All rows ignoring the filter
- [ ] The rows removed by WHERE
- [ ] A boolean

*Answer:* The number of rows in each group hired since 2022. Conditional aggregation.