Filtering, Aggregation, and Grouping

Why Aggregation Matters in Data Analytics

Raw tables store one fact per row. A table of library checkouts might have hundreds of thousands of rows, each recording a single transaction. Decision-makers rarely need every row; they need summaries: How many books did each branch lend last month? Which patrons check out the most items? Which branches are underperforming? SQL's aggregation tools let you collapse many rows into meaningful summary numbers without ever leaving the database.

Aggregate Functions

PostgreSQL (and standard SQL) provides five core aggregate functions you will use constantly:

• COUNT(*) — counts all rows in a group, including rows with NULL values in other columns.
• COUNT(column) — counts only non-NULL values in that column.
• SUM(column) — adds up all numeric values in the column.
• AVG(column) — returns the arithmetic mean of non-NULL numeric values.
• MIN(column) / MAX(column) — return the smallest or largest value, and work on numbers, dates, and text alike.

These functions appear in the SELECT list. On their own, without GROUP BY, they collapse the entire result set into a single summary row:

```sql

SELECT COUNT(*) AS total_checkouts,

AVG(days_borrowed) AS avg_days

FROM checkouts;

```

GROUP BY: Splitting Results into Groups

When you add GROUP BY, PostgreSQL divides the rows into subsets and applies the aggregate function to each subset independently. Every column in the SELECT list must either appear inside an aggregate function or be listed in the GROUP BY clause — otherwise the query will raise an error, because the engine would not know which row's value to display for that column.

```sql

SELECT branch_name,

COUNT(*) AS total_checkouts

FROM checkouts

GROUP BY branch_name

ORDER BY total_checkouts DESC;

```

This returns one row per branch with the total checkouts for that branch.

WHERE vs. HAVING: A Critical Distinction

This is the most common source of errors for new SQL writers, and it appears on certification exams:

• WHERE filters individual rows before groups are formed. It cannot reference aggregate functions because aggregation has not happened yet.
• HAVING filters groups after aggregation is complete. It can reference aggregate functions.

Mnemonic: WHERE comes Where the raw data lives; HAVING comes Hafter (after) aggregation.

```sql

-- Correct: filter raw rows with WHERE, then group

SELECT branch_name, COUNT(*) AS total

FROM checkouts

WHERE checkout_year = 2023

GROUP BY branch_name

HAVING COUNT(*) > 500;

```

The WHERE clause removes rows from years other than 2023 before any grouping occurs. The HAVING clause then keeps only branches whose 2023 count exceeds 500. Swapping HAVING for WHERE here would cause an error because WHERE cannot evaluate COUNT(*).

Query Execution Order

Understanding the logical order in which PostgreSQL processes clauses prevents many mistakes:

1. FROM — identify the table(s)

2. WHERE — filter rows

3. GROUP BY — form groups

4. HAVING — filter groups

5. SELECT — compute output columns and aliases

6. ORDER BY — sort results

7. LIMIT — restrict row count

Because SELECT is evaluated at step 5, column aliases created in SELECT are not available in WHERE or HAVING in standard SQL. In PostgreSQL, ORDER BY can reference SELECT aliases, but HAVING generally cannot.

Combining Filters and Aggregates

You can use WHERE and HAVING together in the same query. A realistic example: find branches that served more than 200 patrons in the first quarter of 2024:

```sql

SELECT branch_name,

COUNT(DISTINCT patron_id) AS unique_patrons

FROM checkouts

WHERE checkout_date BETWEEN '2024-01-01' AND '2024-03-31'

GROUP BY branch_name

HAVING COUNT(DISTINCT patron_id) > 200

ORDER BY unique_patrons DESC;

```

COUNT(DISTINCT patron_id) counts each patron only once per branch even if they checked out multiple items.

Readability and Professional Standards

In professional settings, readable SQL is as important as correct SQL. Follow these conventions:

• Write SQL keywords in uppercase (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY).
• Use meaningful aliases with AS.
• Indent continuation lines two to four spaces.
• Add a comment for any non-obvious logic using -- or / /.

Data Integrity and Safety Notes

Data analytics work does not involve physical hazards in the traditional sense, but it carries its own professional safety obligations:

• Never run UPDATE, DELETE, or DROP statements on production data without explicit authorization and a verified backup. In this course you will only query the practice library database, but form the habit now.
• Verify row counts before and after transformations. A query that silently drops rows due to an incorrect JOIN or WHERE condition produces wrong summaries — a form of analytic error that can mislead real decisions.
• Protect personally identifiable information (PII). Even in practice datasets, treat patron IDs and names as if they were real. Do not share query results containing PII outside the classroom environment.

Summary

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse multiple rows into single summary values. GROUP BY splits the data into subsets before aggregation. WHERE filters raw rows before grouping; HAVING filters groups after aggregation. Keeping the logical execution order in mind and writing readable, well-commented SQL are foundational professional skills that every credential exam will test.