SQL GROUP BY & Aggregation: HAVING, COUNT, FILTER & ROLLUP

Q: What's the difference between WHERE and HAVING?

WHERE filters individual rows before they're grouped, and can't reference aggregates. HAVING filters groups after aggregation, and can. Use WHERE whenever possible for performance; use HAVING for conditions like "customers with more than 5 orders".

Q: COUNT(*) vs COUNT(1) vs COUNT(column)?

COUNT() and COUNT(1) are equivalent: both count rows, and databases optimize them identically. COUNT(column) counts only rows where that column isn't NULL, which is useful for counting matched rows in outer joins or filled-in values.

Q: How do I count distinct values efficiently on huge tables?

Exact COUNT(DISTINCT) requires tracking every value and gets expensive at scale. Warehouses offer approximate functions (APPROX_COUNT_DISTINCT, HyperLogLog sketches) with about 1% error at a fraction of the cost. For repeated queries, maintain pre-aggregated sketches or rollup tables.

Q: How do I pivot rows into columns?

Use conditional aggregation: SUM(CASE WHEN month = 1 THEN revenue END) AS jan, … grouped by the row key. Some databases also offer PIVOT (SQL Server, Snowflake, DuckDB) or crosstab (PostgreSQL's tablefunc extension).

SQL GROUP BY & Aggregation

Aggregation turns many rows into summary values: total revenue, orders per day, average order value per country, number of active users. GROUP BY defines the groups, aggregate functions (COUNT, SUM, AVG, MIN, MAX, and friends) compute a value per group, and HAVING filters the groups. Almost every dashboard, report, and analytics query is built on these.

The rules are simple, but the details trip people up: which COUNT to use, where to filter (WHERE vs HAVING), how NULLs affect averages, why joins inflate sums, and how to compute several conditional metrics in one pass. This page covers them, plus GROUPING SETS, ROLLUP, and CUBE for subtotals.

TL;DR

Every column in SELECT must be grouped or aggregated (or functionally dependent on a grouped primary key, in PostgreSQL).
WHERE filters rows before grouping; HAVING filters groups after.
COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts unique non-NULL values.
Aggregates ignore NULLs (except COUNT(*)), so AVG averages only non-NULL values.
Conditional aggregation (COUNT(*) FILTER (WHERE …) or SUM(CASE WHEN … THEN 1 ELSE 0 END)) computes many metrics in one scan.
ROLLUP, CUBE, and GROUPING SETS produce subtotals and grand totals in one query.

Quick Example

A daily sales summary with conditional metrics:

Subtotals by region and country, plus a grand total:

Core Concepts

Aggregate Functions

GROUP BY Rules

Once you group, each result row represents a group. Selected columns must be:

listed in GROUP BY, or
inside an aggregate function, or
functionally dependent on a grouped primary key (PostgreSQL and standard SQL allow SELECT c.name … GROUP BY c.id when id is the primary key).

MySQL historically allowed ungrouped columns and returned arbitrary values. ONLY_FULL_GROUP_BY (the default since 5.7) rejects that. You can group by expressions (DATE_TRUNC('month', placed_at)), and some databases accept column positions or aliases (GROUP BY 1), which is convenient but fragile.

WHERE vs HAVING

Put row-level conditions in WHERE. It's evaluated first, can use indexes, and reduces the work of grouping. Use HAVING only for conditions on aggregates.

Conditional Aggregation

Compute many metrics in a single pass instead of several queries or self-joins:

This is also how you pivot rows into columns without a PIVOT operator.

GROUPING SETS, ROLLUP, and CUBE

GROUPING SETS ((a, b), (a), ()): several groupings in one query, the union of GROUP BY a, b, GROUP BY a, and a grand total.
ROLLUP (a, b): hierarchical subtotals, meaning (a, b), (a), (). Suits region → country, or year → month.
CUBE (a, b): all combinations, meaning (a, b), (a), (b), ().
GROUPING(col) distinguishes subtotal NULLs from real NULL values.

They avoid UNION ALL of several aggregate queries, which is common in reporting and data warehousing.

Best Practices

Aggregate Before Joining One-to-Many Paths

Joining orders to items and payments before summing multiplies rows and inflates totals. Aggregate each child table to one row per parent first (in a CTE), then join. See SQL joins.

Handle NULLs Explicitly

Wrap sums in COALESCE(SUM(x), 0) when "no rows" should mean zero, and decide whether AVG should ignore NULLs or treat them as zero (AVG(COALESCE(x, 0))). See NULL handling.

Mind Integer Division

SUM(paid) / COUNT() with integer columns truncates to 0 in many databases. Cast (SUM(paid)::numeric / COUNT()) or multiply by 1.0.

Pre-Aggregate Hot Queries

Dashboards that repeatedly aggregate large tables benefit from materialized views, summary tables, or warehouse models refreshed on a schedule, rather than scanning raw events on every page load.

Common Mistakes

Filtering Aggregates in WHERE

COUNT(*) After a LEFT JOIN

Averaging Averages

The average of per-day averages isn't the overall average when days have different row counts. Compute from sums and counts: SUM(total) / SUM(order_count).

FAQ

What's the difference between WHERE and HAVING?

WHERE filters individual rows before they're grouped, and can't reference aggregates. HAVING filters groups after aggregation, and can. Use WHERE whenever possible for performance; use HAVING for conditions like "customers with more than 5 orders".

COUNT(*) vs COUNT(1) vs COUNT(column)?

COUNT(*) and COUNT(1) are equivalent: both count rows, and databases optimize them identically. COUNT(column) counts only rows where that column isn't NULL, which is useful for counting matched rows in outer joins or filled-in values.

How do I count distinct values efficiently on huge tables?

Exact COUNT(DISTINCT) requires tracking every value and gets expensive at scale. Warehouses offer approximate functions (APPROX_COUNT_DISTINCT, HyperLogLog sketches) with about 1% error at a fraction of the cost. For repeated queries, maintain pre-aggregated sketches or rollup tables.

How do I pivot rows into columns?

Use conditional aggregation: SUM(CASE WHEN month = 1 THEN revenue END) AS jan, … grouped by the row key. Some databases also offer PIVOT (SQL Server, Snowflake, DuckDB) or crosstab (PostgreSQL's tablefunc extension).