GROUP BY & Aggregates

In plain English

Collapse many rows into one summary per group — totals per customer, averages per day, counts per category.

Why it's worth your time

Every metric, every feature, every dashboard number is an aggregation, and the WHERE/HAVING distinction is a standard interview check.

If you remember three things

• GROUP BY defines the buckets; the aggregate summarizes each
• WHERE filters rows before grouping; HAVING filters groups after
• COUNT(*) counts rows; COUNT(col) skips NULLs

Overview

GROUP BY collapses rows that share a key into one row per group, and aggregate functions — COUNT, SUM, AVG, MIN, MAX — reduce each group to a single value. WHERE filters individual rows before grouping; HAVING filters the resulting groups by their aggregates. The two filters run at different stages and are not interchangeable.

In an interview

GROUP BY buckets rows by a key and computes one aggregate per bucket. WHERE filters rows before grouping and can't see aggregates; HAVING filters groups after aggregation and can. Any non-aggregated column in SELECT must appear in GROUP BY.

Production defaults

Filter early

WHERE before GROUP BY is cheaper than HAVING after

COUNT

COUNT(*) unless you specifically mean 'non-null values of this column'

Averages

AVG ignores NULLs — decide whether a missing value should count as zero

What breaks

• Averages look too high — AVG skips NULLs, so the denominator is smaller than you think. Use COALESCE if zero is the right reading.
• Column not in GROUP BY error — Every non-aggregated selected column must be grouped. Aggregate it, or add it to the GROUP BY.

Watch it explained

Intermediate SQL Tutorial | Having Clause — Alex The Analyst, 3:31

Related

• SELECT & Filtering
• SQL Joins
• Window Functions