3.4Aggregation, grouping, and window functions

Aggregate queries are a common source of bugs that "look correct but the numbers are subtly off." Much of the cause traces back to how NULL is handled (the difference between COUNT(*) and COUNT(column)) and to misunderstanding the order in which WHERE and HAVING apply. Window functions, meanwhile, are fundamentally different tools from GROUP BY in that they attach an aggregate value to each row without collapsing rows, and choosing between the two is a common judgment call. This section covers these from the perspective of practical report building.

3.4.1GROUP BY and HAVING

• GROUP BY groups rows by the values of specified columns and applies aggregate functions per group. HAVING filters based on the aggregated result after grouping (e.g., COUNT(*) > 10). WHERE differs from HAVING in that it filters individual rows before aggregation.
• The logical order of processing is conceptually FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Writing a condition on pre-aggregation individual-row values directly in HAVING produces unintended results, so any filtering that should happen before aggregation must be written in WHERE (filtering earlier with WHERE also reduces what is aggregated, which is more efficient).

3.4.2Aggregate functions and how they handle NULL

• COUNT(*) counts all rows, including those with NULL. COUNT(column) counts only rows where that column is not NULL. Confusing the two turns an intended "record count" into an "entries with that field populated" count instead.
• SUM, AVG, MAX, and MIN all exclude NULL from the calculation (they do not treat NULL as zero). If every target row is NULL, the result of SUM/AVG/MAX/MIN is NULL itself (not zero). Not knowing this is a common source of confusion, such as "why does the average come out unexpectedly high or low."

3.4.3Window functions (analytic functions)

• A window function accompanies an OVER() clause and, unlike GROUP BY, does not collapse rows—it attaches, to each row, an aggregate value, rank, or running total computed within the same window (partition). PARTITION BY divides rows into partitions analogous to a group, and ORDER BY specifies the ordering within the window.
• Representative functions: RANK (ties get the same rank, and the next rank is skipped), DENSE_RANK (ties get the same rank, but the next rank is not skipped), and ROW_NUMBER (assigns a unique sequential number even when there are ties). Running totals or moving averages use a frame clause such as SUM(...) OVER (ORDER BY ... ROWS BETWEEN ...).
• A requirement like "get the top-3 sales line items within each department, while retaining the other row-level columns" cannot be met with GROUP BY (which collapses rows, losing the line-item-level columns). The basic decision rule: use a window function when you need ranking while retaining rows, and use GROUP BY when you want rows collapsed down to representative aggregate values.

Suppose an owner of a sales-management system receives a report requirement to "compute this month's average order value" and writes SELECT AVG(amount) FROM orders WHERE order_month = '2026-07', only to notice that the amount column for canceled orders is stored as NULL. Since AVG automatically excludes NULL from its calculation, this formulation computes the average unit price of only the valid (non-canceled) orders. That may look reasonable at first, but if what the business side actually wants is "the effective average order value across all orders including cancellations (a metric that should be pulled down as cancellations increase)," then AVG's behavior of excluding NULL is itself at odds with the requirement. The fix here is to convert the canceled-order amounts to zero via COALESCE(amount, 0) before averaging, or to explicitly rebuild the computation as SUM(COALESCE(amount,0)) / COUNT(*). In addition, suppose the same owner has a further requirement: "list the top-5 line items by order amount within each department, while retaining columns like customer name and order date." This cannot be achieved with GROUP BY, since grouping by department collapses rows and loses line-item-level columns; the appropriate design is to use a window function such as RANK() OVER (PARTITION BY department ORDER BY amount DESC) to attach an in-department rank to every row, then filter in the outer query with WHERE to keep only rows with that rank at or below 5. Judging how NULL should be treated in light of business meaning, and choosing between collapsing rows and preserving them based on the requirement, is the core of aggregation design.

3.4.4Section summary

• WHERE filters individual rows before aggregation, HAVING filters groups after aggregation; the processing order is WHERE -> GROUP BY -> HAVING
• COUNT(*) counts all rows, COUNT(column) counts non-NULL rows only; SUM/AVG/MAX/MIN return NULL if every target row is NULL
• Use a window function when you need ranking or running totals while preserving rows, and GROUP BY when you want rows collapsed