Aggregations — GROUP BY, HAVING, Subqueries

Runtime contract: This review executed the bounded fixtures with Python’s SQLite 3.45.1. The Python lab and portable SQL fixtures execute in an in-memory SQLite database. PostgreSQL 16 ordered-set aggregate syntax is a documented reference, not a successfully executed PostgreSQL query. The local median implementation is exact for the fixture; it never substitutes the midrange for the median.

Why this session exists

Aggregation changes the grain of a result: employee rows become department summaries; sales become month totals. Correctness depends on the input population, NULL policy, grouping key and denominator—not merely on whether SQL compiles.

You will use COUNT/SUM/AVG/MIN/MAX, collection aggregates and percentiles; separate WHERE from HAVING; understand grouped-column rules and their dialect exceptions; use scalar, membership, correlated and derived-table subqueries; and choose exact versus approximate distinct counts using an explicit accuracy contract.

Prerequisites

Read SQL basics and joins. A join can change multiplicity before grouping; aggregation cannot infer which repeated values were unintended.

Buckets, populations and output grain

A library visit log contains one row per visit. A report might group by day, then count visits, distinct visitors or observed dwell times. Those are three different metrics. GROUP BY forms buckets and reduces them to one row per grouping key; a NULL grouping key forms a group rather than being silently discarded.

The ordinary aggregate functions used here have these contracts:

For an ungrouped aggregate over no rows, COUNT returns 0 and these other numeric aggregates return NULL. With ordinary GROUP BY and no input, there are no groups and no output rows. Special grouping constructs such as an empty grouping set have their own semantics. COALESCE changes a NULL value but cannot manufacture an absent group.

SQL aggregation and later grouping extensions have a long history, but support is not universal: GROUPING SETS, ROLLUP and CUBE express multiple subtotal grains, whereas the labs here use ordinary grouping. PostgreSQL's ordered-set percentile syntax does not run in the stock SQLite used for this review. SQLite percentile availability depends on version/build options; use a tested algorithm rather than an invalid syntax attempt followed by a misleading fallback.

Employee fixture and worked department summary

Use Alice 120000, Bob 95000 and Dan 140000 in Eng; Carol 80000 and Grace NULL in HR; Eve 110000 and Frank 60000 in Sales. All seven people count toward headcount; only six contribute observed salaries.

SELECT dept, COUNT(*) AS headcount, COUNT(salary) AS observed,
       SUM(salary) AS total, AVG(salary) AS average
FROM employees
GROUP BY dept
ORDER BY average DESC, dept;

HR's observed average is 80000, not 40000. AVG(COALESCE(salary,0)) would make it 40000 by declaring Grace's missing value zero. That is a new assumption, not a repair. Report completeness alongside the statistic: COUNT(*)-COUNT(salary) gives one missing salary in HR.

A second fixture [10,20,30,NULL,NULL] gives rows=5, observed=3, sum=60 and observed average=20. Treating missing as zero gives 12; use 1.0*SUM(score)/NULLIF(COUNT(*),0) to avoid integer truncation and division by zero, and label the changed denominator. None of these statistics has “four different denominators”: COUNT and SUM are not themselves averages.

WHERE, HAVING and grouped columns

WHERE decides which rows enter the grouping population. HAVING tests the resulting groups. WHERE salary > 70000 GROUP BY dept HAVING COUNT(*) >= 2 keeps three engineers, one HR employee and one salesperson before HAVING, then returns Eng with 3 only. Filtering on salary first is not equivalent to filtering departments by average salary afterward.

For “departments with at least two employees above 90000”:

SELECT dept, COUNT(*) AS n
FROM employees WHERE salary > 90000
GROUP BY dept HAVING COUNT(*) >= 2
ORDER BY dept;

Answer: Eng, 3. PostgreSQL can push suitable HAVING predicates on grouping keys earlier; HAVING is not inherently unindexed or a universal O(groups) physical operation.

The safe portable habit is to select grouped attributes or aggregates. PostgreSQL 16 also recognises some functional dependencies: selecting an employee name while grouping by that table's primary key can be legal. This does not legalise SELECT dept,name,AVG(salary) ... GROUP BY dept when several names belong to a department. SQLite permits “bare” columns and returns a value from some input row, with special min/max rules; do not depend on the particular name observed in one run. An arbitrary name is not the employee who “has the average salary.”

Duplicate values and NULL are independent choices

Run this self-contained SQLite fixture. COUNT(DISTINCT) removes repeated values, not duplicate business events; if two valid events carry the same amount, SUM(DISTINCT amount) would undercount revenue.

WITH observations(g,x) AS (
    VALUES ('A',10),('A',10),('A',NULL),('B',NULL),(NULL,5),(NULL,5)
)
SELECT g,COUNT(*) AS rows_n,COUNT(x) AS observed,
       COUNT(DISTINCT x) AS distinct_known,SUM(x) AS total,AVG(x) AS mean
FROM observations GROUP BY g
ORDER BY g IS NULL,g;

Answer: ('A',3,2,1,20,10.0), ('B',1,0,0,NULL,NULL), (NULL,2,2,1,10,5.0). The NULL-key group survives; the all-NULL B group survives with no observed values. Add HAVING COUNT(x)>0 to remove B, not WHERE x IS NOT NULL if you still need its missingness count. An empty input without GROUP BY returns one aggregate row; the same input with GROUP BY returns none.

The correlated maximum and grouped-join queries below assume the declared NOT NULL dept. If a nullable key is meant to identify one missing-department bucket, x.dept=e.dept does not match NULL to NULL even though GROUP BY collects them. PostgreSQL 16 can use IS NOT DISTINCT FROM; SQLite can use IS; MySQL uses <=>. Decide whether missing department is a meaningful bucket before applying a null-safe join.

MySQL 8.0 contract: ONLY_FULL_GROUP_BY is enabled by default and rejects nonaggregated columns that are neither grouped nor functionally dependent, with documented single-value exceptions. Disabling that mode permits an arbitrary group value; an outer ORDER BY does not choose which name supplies that value. ANY_VALUE(name) suppresses the check but does not turn the chosen name into a maximum-salary employee. This is a source-qualified MySQL rule, not an outcome proved by SQLite. See MySQL grouping rules.

Subqueries: shape and correlation are different axes

A scalar subquery yields one column from at most one row. PostgreSQL errors on multiple rows; SQLite uses the first row, a portability hazard. A zero-row scalar result is NULL. An aggregate without GROUP BY is a common way to guarantee a single row.

SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC, id;

The company observed mean is 605000/6 = 100833.333…; Dan, Alice and Eve exceed it.

IN tests membership in a single-column subquery, while EXISTS tests whether any row exists, not a returned “list of values.” A correlated query references an outer binding and can be scalar or existential. A derived table supplies an entire relation in FROM; a CTE gives such a stage a name. These are not three mutually exclusive kinds.

SELECT e.name, e.dept, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(x.salary) FROM employees x WHERE x.dept=e.dept)
ORDER BY e.dept, e.id;

Answer: Dan, Carol, Eve. Add another engineer at 140000 and both engineers match. A ROW_NUMBER()=1 rewrite would keep only one and is not semantically equivalent unless that tie policy is intended. An all-NULL department has no matching maximum under =.

The equivalent grouped-join rewrite is:

SELECT e.name, e.dept, e.salary
FROM employees e
JOIN (SELECT dept, MAX(salary) AS max_sal FROM employees GROUP BY dept) m
  ON m.dept=e.dept AND m.max_sal=e.salary
ORDER BY e.dept, e.id;

It exposes a useful aggregate-then-join shape but does not guarantee a particular hash plan or one physical execution. The optimiser may decorrelate, cache, rescan or probe an index. Compare actual plans and preserve tie/NULL semantics before claiming a rewrite is faster.

Three aggregation failures, solved

Missing is not automatically zero

SUM(salary) WHERE dept='Legal' returns NULL; COALESCE(...,0) returns 0 by reporting policy. To emit every product/week pair, form a bounded dimension grid, then LEFT JOIN a preaggregated sales relation at that grain. Only substitute zero when the source period is known complete. An ingestion outage should carry a missing/incomplete flag, not masquerade as no sales.

For two products and two weeks with only one observed sale of 10 for product A/week1, a complete grid produces (A,w1,10),(A,w2,0),(B,w1,0),(B,w2,0) under a known-complete-data policy. Aggregating the sparse sales table alone produces just one row. This solves absence of groups as well as NULL values.

Average of averages loses weights

The employee department means average to 94444.444…, not the original lesson's 94167. The company mean is 100833.333…. HR's one observed salary receives the same weight as Eng's three if means are averaged blindly.

Carry (sum, non-null count) per group, then compute total sum divided by total count. Region A with one order worth 100 and region B with nine orders totalling 90 have means 100 and 10. Their unweighted mean is 55; the global order mean is 190/10=19. For US one million orders averaging 50 and Australia one hundred averaging 500, the global mean is about 50.044996, not 275. Weight by the denominator that defined each mean, not an unrelated headcount.

Distinct counts do not add across overlapping groups

Day 1 users {A,B} and day 2 {B,C} have counts 2 and 2; the combined distinct count is 3, not 4. Sum and row count combine over disjoint populations; even they are wrong if rollups overlap. Ratios need numerator/denominator, percentiles need richer distribution information, and distinct counts need identity/overlap information.

Several subtotal grains in one report

The useful historical progression is from one grouping grain to several explicitly requested grains, and from exact statistics to mergeable approximations—not a claim that every engine implements every SQL-standard feature. PostgreSQL 16 supports GROUPING SETS, ROLLUP and CUBE; these are reference-only, not stock SQLite 3.45.1 syntax.

-- PostgreSQL 16 reference; sales(region,product,amount) is a separate fixture.
SELECT region,product,SUM(amount) AS revenue,
       GROUPING(region,product) AS grouping_mask
FROM sales
GROUP BY GROUPING SETS ((region,product),(region),())
ORDER BY grouping_mask,region,product;

For sales (East,A,10),(East,B,20),(West,A,5), the detail grain has revenues 10,20,5; regional subtotals are East=30 and West=5; the empty grouping set produces the grand total 35. A missing grouping key is output as NULL, but a real NULL region in source data is a different case. GROUPING(region,product) marks omitted grouping expressions: detail=0, region subtotal=1, grand total=3. Do not identify a subtotal with product IS NULL alone. ROLLUP(region,product) expresses these hierarchical grains; CUBE(region,product) additionally requests product-only subtotals (A=15,B=20, mask=2). Summing every displayed row would count the same sales several times. See PostgreSQL grouping sets.

Exact, approximate and precomputed summaries

SUM, COUNT, MIN and MAX admit small mergeable partial states; AVG uses (sum,count). State and traffic still grow with the number of grouping keys, and floating-point addition is not exactly associative. Group cardinality, skew and distribution determine whether partial aggregation saves much traffic.

For arbitrary high-cardinality values, exact distinctness cannot be recovered just from local counts. It may require sets, sorting or other engine-specific strategies. Exact median likewise cannot generally be reconstructed from fixed-size local medians. This does not prove every exact implementation must always shuffle every original value: prepartitioning, bounded domains and existing order can matter.

HyperLogLog-family sketches retain compact mergeable cardinality state at a chosen precision. In BigQuery, HLL_COUNT functions expose sketch initialisation, merging and extraction; APPROX_COUNT_DISTINCT is a different interface that does not expose the same reusable sketch. Sketch compatibility includes hash/type/precision conventions. Merge sketches across days; do not sum their extracted estimates.

Approximation error is statistical and implementation/precision-dependent, not a universal guaranteed 2% bound or 12 KB allocation. Identical inputs under a deterministic implementation need not fluctuate between runs. Fixed precision limits per-sketch state, not total memory across millions of groups. Use exact counts for contracts requiring exact identity accounting; use approximation for an accepted uncertainty budget, regardless of an arbitrary 100M-row threshold.

A rollup table pays computation at refresh time and helps stable common query shapes, but introduces freshness, late-data and dimension-coverage obligations. Exact distinct sets, compatible sketches or recomputation can support combined ranges; a stored daily count alone cannot.

Percentiles, strings and actual median

For sorted non-null values, a continuous percentile interpolates at a fractional position. PostgreSQL 16 provides percentile_cont(0.5) WITHIN GROUP (ORDER BY salary); percentile_disc selects an actual observed value. A latency report should choose the convention and population, and may show mean and p95: neither is universally the wrong statistic.

For Eng [95000,120000,140000], median is 120000, but (min+max)/2 is 117500. For Sales [60000,110000], the continuous median is 85000; HR's is 80000. The exact SQLite median below numbers sorted non-null rows, counts each partition, then averages its one or two middle rows. An all-null group must be restored from a department dimension if it should appear with NULL median.

Collection order also needs a contract. The lab supplies a sorted subquery to SQLite GROUP_CONCAT rather than asserting accidental insertion order; that exact query's output is tested here. Do not assume its order survives arbitrary joins or query rewrites. SQLite 3.44+ supports aggregate-local ordering, so on this review's 3.45.1 runtime the more explicit form is GROUP_CONCAT(name, ', ' ORDER BY name,id). This syntax is not available on the lab's older 3.30 minimum (needed for NULLS LAST). PostgreSQL uses string_agg(name, ', ' ORDER BY name,id). ARRAY_AGG and STRING_AGG do not share the same NULL contract. See the SQLite 3.44 release.

Hands-on: grouped reports and subquery equivalence

Save as agg_lab.py. The deliberately invalid-in-PostgreSQL bare-column example is labelled as a SQLite behaviour demonstration, not a portable answer. Its arbitrary name is not tested as a stable result.

"""agg_lab.py — GROUP BY, HAVING, subqueries, and the classic traps."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept TEXT NOT NULL,
    salary INTEGER,               -- nullable on purpose
    hired TEXT
);
INSERT INTO employees VALUES
    (1,'Alice','Eng',   120000,'2020-03-01'),
    (2,'Bob',  'Eng',    95000,'2021-06-15'),
    (3,'Carol','HR',     80000,'2019-01-10'),
    (4,'Dan',  'Eng',   140000,'2018-09-20'),
    (5,'Eve',  'Sales', 110000,'2022-02-01'),
    (6,'Frank','Sales',  60000,'2023-11-05'),
    (7,'Grace','HR',       NULL,'2024-05-12');
""")
 
def run(label: str, sql: str) -> None:
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print(" ", row)
 
# ---- 1. Basic GROUP BY with multiple aggregates ----
run("Headcount, non-null salary count, and avg per dept", """
    SELECT dept,
           COUNT(*)               AS headcount,
           COUNT(salary)          AS with_salary,
           SUM(salary)            AS total_pay,
           AVG(salary)            AS avg_pay,
           MIN(salary)            AS min_pay,
           MAX(salary)            AS max_pay
    FROM employees
    GROUP BY dept
    ORDER BY avg_pay DESC NULLS LAST;
""")
 
# ---- 2. HAVING — filters groups after aggregation ----
run("Depts with avg salary > 90k", """
    SELECT dept, AVG(salary) AS avg_pay
    FROM employees GROUP BY dept
    HAVING AVG(salary) > 90000;
""")
 
# ---- 3. WHERE vs HAVING — filter before AND after ----
run("Filter rows first, then groups: high earners only, dept with >=2 remaining", """
    SELECT dept, COUNT(*) AS n_high_earners
    FROM employees
    WHERE salary > 70000              -- row filter (WHERE)
    GROUP BY dept
    HAVING COUNT(*) >= 2;             -- group filter (HAVING)
""")
 
# ---- 4. Scalar subquery — above the company average ----
run("Employees earning above company average", """
    SELECT name, salary,
           (SELECT ROUND(AVG(salary), 0) FROM employees) AS company_avg
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
    ORDER BY salary DESC;
""")
 
# ---- 5. Correlated subquery — top earner per dept ----
run("Top earner per dept (correlated subquery)", """
    SELECT e.name, e.dept, e.salary
    FROM employees e
    WHERE e.salary = (
        SELECT MAX(salary) FROM employees WHERE dept = e.dept
    )
    ORDER BY e.dept;
""")
 
# ---- 6. IN subquery ----
run("Depts that have anyone earning over 100k", """
    SELECT DISTINCT dept
    FROM employees
    WHERE dept IN (
        SELECT dept FROM employees WHERE salary > 100000
    );
""")
 
# ---- 7. COUNT(DISTINCT) — exact but expensive ----
run("Number of distinct salary values", """
    SELECT COUNT(DISTINCT salary) AS n_distinct_salaries FROM employees;
""")
 
# ---- 8. STRING_AGG / GROUP_CONCAT ----
run("Comma-joined names per dept", """
    SELECT dept, GROUP_CONCAT(name, ', ') AS names
    FROM (SELECT dept,name FROM employees ORDER BY dept,name,id) ordered_names
    GROUP BY dept ORDER BY dept;
""")
 
# ---- 9. Exact continuous median over known salaries ----
run("Exact median per dept (one or two middle observations)", """
    WITH ranked AS (
        SELECT dept,salary,
               ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary,id) AS rn,
               COUNT(*) OVER (PARTITION BY dept) AS n
        FROM employees WHERE salary IS NOT NULL
    )
    SELECT dept,AVG(salary) AS median FROM ranked
    WHERE rn IN ((n+1)/2,(n+2)/2)
    GROUP BY dept ORDER BY dept;
""")
 
# ---- 10. TRAP: SUM over empty set is NULL, not 0 ----
run("SUM of no matching rows → NULL", """
    SELECT SUM(salary) FROM employees WHERE dept = 'Legal';
""")
run("Fix: COALESCE(SUM(x), 0)", """
    SELECT COALESCE(SUM(salary), 0) FROM employees WHERE dept = 'Legal';
""")
 
# ---- 11. TRAP: ‘average of averages’ ----
run("WRONG: average of per-dept averages (not the true company average)", """
    SELECT AVG(avg_pay) AS avg_of_avgs FROM (
        SELECT AVG(salary) AS avg_pay FROM employees GROUP BY dept
    );
""")
run("RIGHT: single-pass overall average", """
    SELECT AVG(salary) AS true_company_avg FROM employees;
""")
 
# ---- 12. Strict-mode violation — will error in Postgres, silent in SQLite ----
run("SQLite lets this pass; Postgres rejects (name not in GROUP BY / aggregate)", """
    SELECT dept, name, AVG(salary) FROM employees GROUP BY dept;
""")
 
conn.close()

Check headcounts (3,2,2), observed counts (3,1,2), six distinct known salaries, exact department medians, and both versions of the top-earner query. The empty aggregate gives NULL/0 according to function; the grouped empty relation yields no rows. These are exact fixture results, not timings.

Monthly revenue exercise and answer

Extend the lab before closing the connection:

CREATE TABLE sales(id INTEGER PRIMARY KEY, salesperson_id INTEGER REFERENCES employees(id),
                   amount INTEGER NOT NULL, sold_at TEXT NOT NULL);
INSERT INTO sales VALUES
 (1,5,5000,'2026-01-15'),(2,5,3000,'2026-01-22'),
 (3,6,1000,'2026-02-05'),(4,5,4000,'2026-02-20'),
 (5,6,6000,'2026-03-01'),(6,5,7000,'2026-03-10');
SELECT e.dept, strftime('%Y-%m',s.sold_at) AS month,
       COUNT(*) AS deals, SUM(s.amount) AS revenue
FROM sales s JOIN employees e ON e.id=s.salesperson_id
GROUP BY e.dept, strftime('%Y-%m',s.sold_at)
ORDER BY month,e.dept;

Answer: Sales has January (2,8000), February (2,5000), March (2,13000). Add HAVING SUM(s.amount)>5000 to retain January and March; February equals the boundary and is excluded. Each sale matches one employee, so this join preserves sale grain. For PostgreSQL timestamp columns, use date_trunc('month', sold_at) with the reporting timezone policy defined; SQLite strftime on ISO TEXT is not that type system.

Revenue per month without a department dimension

For the original orders(order_date,amount) question, use strftime('%Y-%m',order_date) in SQLite:

WITH orders(order_date,amount) AS (
    VALUES ('2026-01-01',10),('2026-01-31',20),('2026-02-01',7)
)
SELECT strftime('%Y-%m',order_date) AS month,SUM(amount) AS revenue
FROM orders GROUP BY strftime('%Y-%m',order_date) ORDER BY month;

Answer: January=30, February=7. PostgreSQL 16's corresponding expression is date_trunc('month',order_date) on an appropriate timestamp column; set the reporting timezone for timestamptz. Invalid/mixed-format text dates need validation before grouping, not silent acceptance as a NULL month.

Recall and production decisions

• WHERE versus HAVING: eligible rows versus accepted groups. An outer query can also filter a derived aggregate, so HAVING is not the only syntax for the business condition.
• COUNT star versus column: HR 2 versus 1; no need to COALESCE a standalone COUNT to prevent empty-result NULL.
• Blank report cells: distinguish an absent group, an empty aggregate, an all-null population and a known zero before choosing a grid or COALESCE.
• Non-grouped names: either change grain, use a meaningful aggregate/selection rule or exploit a valid dialect-supported functional dependency; never trust an arbitrary SQLite name.
• Approximation choice: choose from cost and tolerated error, not a slogan that exact distinct count is a code smell.
• Correlated maximum: can return tied winners; a ROW_NUMBER replacement changes that policy and is not automatically O(n).
• Weighted means: preserve sum and observed count; the 100/90 regional example gives 19.
• Missing revenue replaced with zero: expose eligible count, observed count, missingness and source completeness so an outage is not mistaken for lower sales.

The original company runtime/error percentages are not established benchmarks. Their substantive lessons survive as the distinct-count overlap example, exact weighted arithmetic and complete dimension-grid mechanism. Do not infer that a finance total becomes correct merely because SQL returned a non-null number.

Explain-out-loud answer guide

1. What is aggregation? Put eligible rows into key-defined buckets and compute one summary row per bucket; ordinary grouping loses individual-row detail.

2. WHERE versus HAVING? WHERE tests input rows before grouping; HAVING tests groups after aggregation. The high-earner query gives Eng=3, not the headcount of every department.

3. What unexpectedly returns NULL? SUM over no matching rows returns NULL. COALESCE may report zero under an explicit policy; use a dimension grid to create missing groups, not just to replace a NULL cell.

Where this leads and sources

Windows retain row detail, CTEs name stages, and indexes can sometimes supply useful ordering. Dimensional reports, monitoring distributions and mergeable sketches extend these population/grain contracts; not every p95 metric is implemented as PERCENTILE_CONT.

Primary documentation inspected 10 October 2026: PostgreSQL 16 aggregates, SELECT grouping rules, subquery expressions, SQLite aggregate functions, SQLite SELECT bare-column rules and BigQuery HLL_COUNT. Mutable sources are dated snapshots; BigQuery, PostgreSQL and distributed sketches were not executed here.

Optional archived resources

These original resource destinations are retained for continuity, not as independently verified technical evidence. Historical video labels, attribution, durations and playback were not verified in this review; search links are discovery queries, not specific lectures. Repeated links are listed once. Prefer the versioned primary sources above for dialect contracts; the archived current-version documentation may evolve.

• Group By + Order By in MySQL | Beginner MySQL Series
• SQL Aggregation & GROUP BY
• Aggregate Functions — PostgreSQL Tutorial
• SQL Aggregate Functions — Interactive
• HyperLogLog in Practice