Aggregation With GROUP BY and HAVING

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.

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.

Quick check: Where do you filter on sum(...) > 1000?

• In ORDER BY
• In WHERE
• In HAVING
• In LIMIT