Aggregations — GROUP BY, HAVING, COUNT, SUM

Aggregation is the process of collapsing many rows into a single summary row using mathematical functions. Without aggregation, every analytical question — total runs in a tournament, average strike rate by country, number of centuries per player — would require retrieving every raw row and computing the answer in application code. SQL aggregation pushes this computation into the database engine, which processes data in bulk, close to storage, orders of magnitude faster than transferring rows to an application and aggregating there.

GROUP BY partitions rows into groups sharing a common value before applying aggregate functions to each group. HAVING filters the resulting groups — it is the WHERE clause for aggregated results, evaluated after aggregation rather than before. Together, GROUP BY and HAVING form the foundation of every reporting query, data quality check, and KPI calculation in analytical engineering. Misunderstanding the interaction between GROUP BY, HAVING, and WHERE is one of the most common sources of incorrect aggregations in production dashboards.

For data engineers, mastering aggregation is non-negotiable. Virtually every data warehouse transformation involves summarising transactional data into dimensional models: total revenue per product per day, unique users per cohort per week, p99 latency per service per hour. All of these are GROUP BY queries. Writing them correctly — with the right grouping keys, the right aggregate functions, and the right NULL handling — determines whether business dashboards show correct numbers or subtly wrong ones.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.