PyCodeItPython trace & SQL practice
SQL Analytics TrackWebAssembly SQLite Playground

Aggregations & Group Filtering

Aggregate datasets using COUNT, SUM, AVG, and filter consolidated data groups using HAVING.

Relational Theory & Optimization Architecture

SQL Aggregations condense raw transactional records into actionable summary statistics. By orchestrating aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY partitions and HAVING threshold filters, data engineers compute organizational KPIs and analytical dimensions. Mastery of aggregation requires recognizing how database engines manage grouping hash buckets and handle NULL values across columns.

Physical Storage & Buffer Pool Mechanics

When executing a GROUP BY query, the engine can implement Hash Aggregation or Stream Aggregation. In Hash Aggregation, an in-memory hash table is populated using the grouping columns as the composite hash key; each new row updates the running accumulator for its group. In Stream Aggregation (chosen when the input is pre-sorted via an index), the engine simply scans sequentially, emitting a summary row whenever the grouping key changes.

Aggregation Pipeline: Partitioning and Post-Filtering

The aggregation pipeline adheres to a strict logical sequence: FROM and JOIN gather matching rows; WHERE filters individual tuples before grouping; GROUP BY partitions remaining tuples into distinct grouping buckets; aggregate functions compute accumulators over each bucket; HAVING applies predicate filters to the aggregated group results; and SELECT projects the permitted columns.

Worked Query Walkthrough: Regional Order Volume and Average Revenue

SELECT region, COUNT(*) AS total_orders, AVG(order_total) AS avg_revenue
FROM orders
WHERE status = 'DELIVERED'
GROUP BY region
HAVING COUNT(*) >= 2 AND AVG(order_total) > 100.0;

Step-by-Step Row Evaluation Matrix

regionorder_idorder_totalstatusStep 1: WHERE FilterStep 2: Bucket AccumulationStep 3: HAVING Evaluation
East1150.0DELIVEREDPASSEast: count=1, sum=150.0[Evaluating...]
East2250.0DELIVEREDPASSEast: count=2, sum=400.0 (avg=200)PASS (count>=2 & avg>100)
West350.0DELIVEREDPASSWest: count=1, sum=50.0[Evaluating...]
West470.0DELIVEREDPASSWest: count=2, sum=120.0 (avg=60)FAIL (avg <= 100.0)
North5300.0CANCELLEDFAILNorth: not accumulatedFAIL (count < 2)

Runtime Trace Analysis

Rows with status != 'DELIVERED' are purged in the WHERE phase. The remaining delivered rows are partitioned into regional buckets. The East bucket yields 2 orders with average $200.0, satisfying both HAVING predicates. The West bucket yields 2 orders with average $60.0, failing the average threshold and being pruned. Only East is returned.

Common Traps, Anti-Patterns & Debugging Solutions

1. Filtering Aggregates in the WHERE Clause

Problematic Syntax (Anti-Pattern)SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department_id;
Optimized AlternativeSELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

The WHERE clause evaluates prior to group creation and aggregate computation. Aggregate functions do not yet exist during the WHERE phase; group-level metrics must be filtered in the HAVING clause.

2. Selecting Non-Aggregated Columns not in GROUP BY

Problematic Syntax (Anti-Pattern)SELECT department_id, employee_name, MAX(salary) FROM employees GROUP BY department_id;
Optimized AlternativeSELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

In ANSI SQL (and standard SQL mode), selecting a column that is neither aggregated nor listed in the GROUP BY clause produces an ambiguous value, resulting in compile-time rejection in Postgres, SQLite, and modern MySQL.

3. Confusing COUNT(*) with COUNT(column)

Problematic Syntax (Anti-Pattern)SELECT COUNT(referral_code) FROM users; -- Expecting total registered user count
Optimized AlternativeSELECT COUNT(*) FROM users; -- Correctly tallies all rows including NULL referrals

COUNT(column) evaluates non-NULL entries only. Users with a NULL referral_code are ignored by COUNT(referral_code), artificially deflating user registration counts.

Production Strategy & Query Engineering Best Practices

A core SQL concept is the operational difference between WHERE and HAVING and how NULLs are treated during aggregation. Always remember that GROUP BY condenses all NULL values into a single group, and that aggregate functions (except COUNT(*)) silently skip NULL entries.

Interactive WebAssembly SQL Playground

Client-Side SQLite Engine
Active Relational Schema & Preloaded Tables
TableKey ColumnsSample Row CountDescription
EmployeeEmpID, Name, DeptID, Salary, ManagerID8 rowsStaff directory with salaries and reporting structure
DepartmentDeptID, DeptName, Budget8 rowsOrganizational departments and annual budgets
ProjectProjectID, ProjectName, DeptID, Deadline5 rowsActive enterprise engineering projects
Interactive Query Workspace

Run custom SQL queries against the Employee, Department, Project schema above or experiment with alternative joins, filters, and aggregations in the interactive editor below.

Frequently Asked Questions on Aggregations & Group Filtering

What is the exact difference between WHERE and HAVING in SQL?

WHERE filters individual matching rows before grouping and aggregation occur. HAVING filters grouped metric buckets after GROUP BY calculations have been completed.

How does COUNT(*) differ from COUNT(column_name)?

COUNT(*) counts every row in the dataset, including rows containing NULL values. In contrast, COUNT(column_name) ignores NULL entries and tallies only rows where the specified column contains non-NULL data.

How does GROUP BY treat NULL values across rows?

SQL engines group all NULL values together into a single distinct group partition, rather than treating each NULL as an independent distinct value.

Does AVG() include rows with NULL values in the denominator?

No. AVG(column) calculates the sum of non-NULL values divided by the count of non-NULL values. Rows where the column is NULL are omitted from both the numerator and denominator.

Explore Other SQL Practice Tracks