There's a point in almost every project where you stop just retrieving rows and start actually asking questions — how many venues per city, which cities have more than a handful, which venues are above average latitude. That's the shift this article is about. The syntax for aggregation isn't complicated on its own. What takes a bit longer to get comfortable with is the order things actually happen in, especially once WHERE and HAVING are both in the same query and doing different jobs.
Where it gets confusing
Say you want cities with more than three venues. The instinct, especially early on, is to write:
SELECT city, COUNT(*) AS venue_count
FROM venues
WHERE COUNT(*) > 3
GROUP BY city;
This fails. Not silently, at least — Postgres will give you an error about aggregate functions not being allowed in WHERE. But the reason it fails is worth actually understanding rather than just memorizing "use HAVING instead." WHERE filters individual rows, and it runs before any grouping happens. At the point WHERE is being evaluated, there's no such thing as "the count for this city" yet, because the rows haven't been grouped into cities at all — they're still just individual venue rows. You can't filter on something that doesn't exist yet.
Aggregate functions and GROUP BY
SELECT COUNT(*) FROM venues;
SELECT AVG(latitude) FROM venues;
SELECT city, COUNT(*) AS venue_count
FROM venues
GROUP BY city;
GROUP BY city collapses all the rows sharing the same city value into a single row per city, and the aggregate functions — COUNT, AVG, and similar — operate on each group rather than the whole table. Fairly intuitive once you've written a few of these.
HAVING, and why it exists at all
SELECT city, COUNT(*) AS venue_count
FROM venues
GROUP BY city
HAVING COUNT(*) > 3;
HAVING is what WHERE would be if it ran after grouping instead of before. It filters groups, not individual rows, and by the time it runs, the counts and averages actually exist as real values you can compare against. That's the whole reason it's a separate keyword rather than Postgres just letting you use aggregates inside WHERE — the two clauses run at genuinely different stages of query execution, filtering different things.
A decent way to remember which is which: WHERE happens before grouping, so it can't see aggregate results yet. HAVING happens after, so it can.
Subqueries — a query inside a query
-- scalar subquery
SELECT name FROM venues
WHERE latitude > (SELECT AVG(latitude) FROM venues);
Here the inner query runs first, computes a single number — the average latitude across all venues — and the outer query uses that number as a comparison value. This is called a scalar subquery because it returns exactly one value.
-- subquery in FROM (a derived table)
SELECT city, avg_lat FROM (
SELECT city, AVG(latitude) AS avg_lat
FROM venues GROUP BY city
) AS city_stats
WHERE avg_lat > 23;
This one's a bit different — the subquery in the FROM clause produces an entire table-like result (city-level averages), which the outer query then filters and selects from, as though it were a real table. It needs an alias (city_stats here) because Postgres requires every table-like thing in a FROM clause to have a name, even a temporary computed one.
-- correlated subquery — references the outer query, runs per row
SELECT v.name FROM venues v
WHERE EXISTS (
SELECT 1 FROM closures c WHERE c.venue_id = v.id
);
This last one is genuinely different from the first two. It's called "correlated" because the inner query references v.id from the outer query — it's not a standalone query you could run on its own, it depends on which outer row is currently being evaluated. Conceptually, Postgres runs this subquery once per row of the outer query, checking "does at least one closure exist for this specific venue." Worth being careful with these on large tables, since running a subquery per row can get expensive if it's not written efficiently.
EXISTS vs IN
EXISTS stops as soon as it finds a single matching row — it doesn't need to know how many matches there are, just whether at least one exists, which also means it handles nulls in the subquery result more predictably. IN builds out the entire subquery result first and then checks membership against it. For "does at least one related row exist" type questions, EXISTS is usually the better fit, both for performance and for correctness around nulls.
CTEs: naming your intermediate steps
WITH closed_venues AS (
SELECT venue_id FROM closures
)
SELECT * FROM venues
WHERE id IN (SELECT venue_id FROM closed_venues);
A CTE — the WITH clause — lets you name a piece of a query and reference it later in the same statement, instead of nesting subqueries inside each other until the query becomes hard to read top to bottom. This particular example is simple enough that it wouldn't really need a CTE, but the value becomes obvious once a query has three or four logical steps chained together — each one gets a name, and the final query reads almost like a short paragraph instead of a wall of nested parentheses.
CTEs are also how you handle recursive queries, using WITH RECURSIVE, which is the standard way to deal with hierarchical data — something like a category tree, where categories can have subcategories that have their own subcategories. That's a bigger topic on its own and probably deserves its own explanation somewhere down the line rather than a paragraph here.
One historical detail worth knowing if it comes up: older versions of Postgres treated CTEs as what's called an "optimization fence" — meaning the planner would essentially compute the whole CTE first, in isolation, before using it in the rest of the query, even if that wasn't the most efficient approach. Since Postgres 12, CTEs are generally optimized more like subqueries, folded into the overall query plan rather than treated as a hard boundary. If someone asks why you'd use a CTE instead of a subquery, readability and reusability within the same query are the main reasons now — the performance difference that used to matter mostly doesn't anymore, on current versions.
Putting a few of these together
Say you want cities where the average venue latitude is unusually high, but only among cities that actually have a meaningful number of venues — filtering out cities with just one or two entries that could skew the average:
WITH city_stats AS (
SELECT city, AVG(latitude) AS avg_lat, COUNT(*) AS venue_count
FROM venues
GROUP BY city
)
SELECT city, avg_lat, venue_count
FROM city_stats
WHERE venue_count >= 5
ORDER BY avg_lat DESC;
The CTE computes both the average and the count per city in one pass, and the outer query then filters on venue_count, which at this point is just a regular column value, not an aggregate anymore — so it goes in WHERE, not HAVING, since the grouping already happened inside the CTE. This is a good example of how naming an intermediate result makes the logic of a query clearer than trying to cram the same thing into one query with HAVING doing double duty.
Where this leaves you
WHERE filters rows, HAVING filters groups, and the reason for the split comes down to when each one actually runs relative to GROUP BY. Subqueries let you use the result of one query inside another, and CTEs let you name those intermediate steps instead of nesting them — mostly for readability at this point, less so for performance on modern Postgres versions.
Next up: indexes — how to actually make these queries fast, and just as importantly, when adding an index doesn't help at all.
Top comments (0)