Week 2: Filtering, Aggregation & GROUP BY

1. Richer WHERE Conditions

setup — an orders table for this week

CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    customer   TEXT NOT NULL,
    status     TEXT NOT NULL,
    total      NUMERIC(10, 2) NOT NULL,
    created_at DATE NOT NULL
);

combining conditions

SELECT * FROM orders WHERE status = 'shipped' AND total > 100;
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

SELECT * FROM orders WHERE status IN ('pending', 'processing'); -- cleaner than chained ORs

SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

SELECT * FROM orders WHERE customer LIKE 'A%';   -- starts with "A"
SELECT * FROM orders WHERE customer ILIKE '%asha%'; -- case-insensitive "contains"

LIKE is case-sensitive; PostgreSQL's ILIKE is the case-insensitive equivalent — % matches any sequence of characters, _ matches exactly one. IN is functionally equivalent to a chain of ORs but reads far more clearly once there are more than two values to check.

2. Aggregate Functions

summarizing an entire table

SELECT COUNT(*) FROM orders;                    -- how many orders exist
SELECT SUM(total) FROM orders;                   -- total revenue across all orders
SELECT AVG(total) FROM orders;                   -- average order value
SELECT MIN(total), MAX(total) FROM orders;        -- cheapest and most expensive order

Each of these collapses many rows into a single value — COUNT(*) counts every row regardless of NULLs; COUNT(column) counts only rows where that specific column is non-NULL, a distinction worth remembering before Week 6 introduces query performance in more depth.

3. GROUP BY & HAVING

An aggregate over the whole table answers one number. GROUP BY answers "one number per category" — the real workhorse of SQL reporting.

revenue and order count, per status

SELECT status, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY status;

HAVING — filtering on the aggregate itself

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 3; -- only customers with more than 3 orders

WHERE filters rows before grouping; HAVING filters groups after aggregation — this is exactly why WHERE COUNT(*) > 3 is a syntax error (WHERE runs before any count exists to compare against) while HAVING COUNT(*) > 3 is correct. Every non-aggregated column in SELECT must appear in GROUP BY — Postgres enforces this and will refuse to run a query that violates it.

4. NULL-Safe Comparisons & COALESCE

COALESCE — the first non-NULL value in a list

SELECT name, COALESCE(nickname, name) AS display_name FROM users;
-- shows the nickname if set, otherwise falls back to the real name

SELECT SUM(COALESCE(discount, 0)) FROM orders;
-- treats a missing discount as 0 rather than letting NULL "infect" the sum

This second example matters more than it looks — SUM and most aggregates already skip NULL values by default, but an expression involving a NULL (like price - discount where discount is NULL) itself evaluates to NULL, silently. COALESCE is the standard fix: give NULL an explicit, sensible fallback before it's used in arithmetic.

5. Hands-on Exercise

Hands-on

Build an orders report

Populate the orders table and write a set of reporting queries combining this week's tools.

Requirements:

Insert at least 15 rows into orders spanning at least 3 different status values and a range of dates across two different months.
A query showing order count and total revenue per status, using GROUP BY.
A query listing only customers with more than 2 orders, using HAVING.
A query finding all orders from a specific customer whose name contains a given substring, case-insensitively, using ILIKE.
Add a nullable discount column to orders, set it on a few rows, leave it NULL on the rest, and write a query computing each order's final total (total - discount) using COALESCE so NULL discounts don't break the calculation.

Hint

If PostgreSQL rejects a GROUP BY query with an error about a column not appearing in the GROUP BY clause or an aggregate function, check every column in your SELECT list — any column that isn't wrapped in an aggregate function (COUNT, SUM, etc.) must appear in the GROUP BY clause exactly, or Postgres has no defined way to pick a single value for it per group.

6. Knowledge Check

Four quick questions. Expand each to check your answer.

Why is `WHERE status IN ('pending', 'processing')` preferred over `WHERE status = 'pending' OR status = 'processing'`, given they return identical results?

They're functionally equivalent, but IN reads far more clearly once there are more than two or three values to check, and it avoids the mistake of accidentally repeating the column name incorrectly or misplacing a parenthesis in a long OR chain. It's a readability and maintainability improvement, not a different result.

What's the practical difference between `COUNT(*)` and `COUNT(email)` on the same table?

COUNT(*) counts every row in the group regardless of any NULLs. COUNT(email) counts only rows where the email column specifically is non-NULL — if some rows have a NULL email, COUNT(email) will be smaller than COUNT(*) for that same group.

Why does `WHERE COUNT() > 3` fail, while `HAVING COUNT() > 3` works correctly?

WHERE filters individual rows before any grouping or aggregation happens, so there's no count yet in existence to compare against at that stage. HAVING runs after GROUP BY has produced its aggregated groups, at which point COUNT(*) for each group is a real, computed value HAVING can filter on.

Why does `price - discount` evaluate to NULL for a row where `discount` is NULL, even though `price` itself has a real value?

Arithmetic and most expressions involving a NULL operand propagate that NULL to the entire expression's result, by the same "comparing/operating on unknown produces unknown" rule that makes age = NULL always false. COALESCE(discount, 0) substitutes a real, known value (0) before the subtraction happens, which is what prevents the NULL from silently propagating into the final total.