# Aggregation With GROUP BY and HAVING — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/q-agg

> Summaries per group.

## WHERE filters rows, HAVING filters groups

`GROUP BY` collapses rows into groups and aggregate functions (`count`, `sum`, `avg`, `min`, `max`, `string_agg`) summarise each group. `WHERE` filters rows **before** grouping; `HAVING` filters groups **after** aggregation. Every selected column must be grouped or aggregated. `count(DISTINCT ...)` avoids double counting when joins multiply rows.

## Revenue by city, excluding cancelled orders, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Cancelled orders are removed with WHERE, revenue is summed per city, and HAVING keeps cities above 1,000: Pune has 3 orders worth 4,556 and Delhi 1 order worth 1,200.

```sql
SELECT c.city,
       count(DISTINCT o.id)          AS orders,
       sum(oi.qty * p.price)         AS revenue
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p    ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.city
HAVING sum(oi.qty * p.price) > 1000
ORDER BY revenue DESC;
```

Output:

```
 city  | orders | revenue 
-------+--------+---------
 Pune  |      3 | 4556.00
 Delhi |      1 | 1200.00
(2 rows)
```

## Filter early with WHERE

Conditions that do not depend on aggregates belong in WHERE, which reduces the rows to group.

**Quiz:** Where do you filter on sum(...) > 1000?

- [ ] In ORDER BY
- [ ] In WHERE
- [x] In HAVING
- [ ] In LIMIT

*Answer:* In HAVING. HAVING works on aggregated groups.