Aggregation, Grouping, Subqueries, and CTEs

Aggregation, Grouping, Subqueries, and CTEs

Once you can retrieve rows from a database, the next step is to summarize them. Analytical questions like "How many books did each branch lend last quarter?" or "Which patron checked out the most items?" require you to collapse many rows into a single answer per group. PostgreSQL gives you powerful tools for this: aggregate functions, `GROUP BY`, `HAVING`, subqueries, and Common Table Expressions (CTEs).

Aggregate Functions

Aggregate functions operate on a set of rows and return one value per group. The five you will use constantly are:

• `COUNT(expression)` — counts non-NULL values; `COUNT(*)` counts all rows including NULLs.
• `SUM(expression)` — totals numeric values.
• `AVG(expression)` — returns the arithmetic mean.
• `MIN(expression)` — returns the smallest value.
• `MAX(expression)` — returns the largest value.

Without `GROUP BY`, the aggregate collapses the entire result set into one row. With `GROUP BY`, you get one row per unique combination of the grouping columns.

GROUP BY and HAVING

`GROUP BY` tells PostgreSQL which column(s) define each group. Every column in your `SELECT` list that is not inside an aggregate function must appear in the `GROUP BY` clause, or the query will error.

```sql

SELECT branch_id, COUNT(*) AS total_loans

FROM loans

GROUP BY branch_id;

```

`WHERE` filters rows before grouping. `HAVING` filters after grouping, so you can use aggregate results as conditions:

```sql

SELECT branch_id, COUNT(*) AS total_loans

FROM loans

GROUP BY branch_id

HAVING COUNT(*) > 500;

```

A common mistake is writing `HAVING total_loans > 500` using a column alias. PostgreSQL evaluates `HAVING` before the alias is finalized in most contexts, so prefer repeating the aggregate expression inside `HAVING`.

Subqueries

A subquery is a `SELECT` statement nested inside another query. There are two main types.

Non-correlated subqueries run once and return a value (or set of values) that the outer query uses. They do not reference any column from the outer query:

```sql

SELECT title

FROM books

WHERE book_id IN (

SELECT book_id FROM loans WHERE return_date IS NULL

);

```

Correlated subqueries reference a column from the outer query, so they re-execute for every row the outer query processes. They are flexible but can be slow on large tables:

```sql

SELECT p.patron_name,

(SELECT COUNT(*) FROM loans l WHERE l.patron_id = p.patron_id) AS loan_count

FROM patrons p;

```

Use correlated subqueries when the logic genuinely depends on each outer row. Otherwise, a `JOIN` or CTE is usually faster and easier to read.

Common Table Expressions (CTEs)

A CTE creates a named, temporary result set at the top of your query using the `WITH` keyword. It exists only for the duration of that statement.

```sql

WITH branch_totals AS (

SELECT branch_id, COUNT(*) AS total_loans

FROM loans

GROUP BY branch_id

)

SELECT b.branch_name, bt.total_loans

FROM branch_totals bt

JOIN branches b ON b.branch_id = bt.branch_id

ORDER BY bt.total_loans DESC;

```

You can chain multiple CTEs by separating them with commas after the first `WITH`. Each later CTE can reference any earlier one. This step-by-step structure makes complex logic far easier to read, test, and maintain than deeply nested subqueries.

Introducing Window Functions: ROW_NUMBER and RANK

Aggregate functions collapse rows. Window functions compute a value for each row while keeping all rows visible. `ROW_NUMBER()` assigns a unique sequential integer to each row within a partition. `RANK()` assigns the same number to ties and then skips numbers.

Both require an `OVER` clause that defines the partition and ordering:

```sql

ROW_NUMBER() OVER (PARTITION BY branch_id ORDER BY loan_count DESC)

```

In the challenge exercise you will wrap this inside a CTE to filter down to a top-5 list. The outer query simply adds `WHERE rn <= 5`.

Trade-offs: Subquery vs. CTE

| Factor | Correlated Subquery | CTE |

|---|---|---|

| Readability | Low for complex logic | High — named, step-by-step |

| Performance | Can be slow (re-executes per row) | Often similar; optimizer may inline |

| Reusability | Cannot reference again | Can be referenced multiple times |

| Debugging | Hard to isolate | Easy — run each CTE alone |

Neither is always faster; use `EXPLAIN ANALYZE` in PostgreSQL to compare actual execution plans.

Safety and Professional Practice

SQL errors rarely cause physical hazards, but data integrity is your responsibility. Always run analytical queries against a development or read-only copy of production data first. Never use `DELETE` or `UPDATE` in the same session as exploratory `SELECT` queries without a transaction (`BEGIN`/`ROLLBACK`) as a safety net. Back up the library dataset before the lab. Keep credentials (database passwords) out of your `.sql` files — use environment variables or a `.pgpass` file instead.

Annotate every query: future maintainers (including yourself) need to understand why you grouped or filtered the way you did, not just what the SQL says.