Aggregating: Intermediate

Know when a query collapses rows

COUNT is the most common aggregate function, and its two forms look nearly identical. Run them on the same column and they can return different numbers, with no error to tell you why.

Understanding the difference between row-level and aggregate functions is essential for writing correct SQL. Functions like SUM(salary * 1.1) work because the row-level expression (salary * 1.1) is wrapped inside an aggregate. Let's understand why this matters and why the reverse doesn't work.

The row-level expression (salary * 1.1) is evaluated independently for every single row. Then SUM collects all those individual results and adds them up per group.

This is why CASE WHEN works inside aggregates. CASE WHEN is a row-level expression, so it evaluates for each row first:

The CASE expression produces a concrete number for each row. SUM doesn't know or care that the number came from a CASE. It just sums whatever values it receives.

Now here's the crucial insight: you cannot put an aggregate inside a row-level expression in the SELECT clause. This is a syntax error:

Row-level expressions in SELECT are evaluated as the aggregate scans each row. The aggregate collects these intermediate results. You can nest row-level inside aggregate (row happens during the scan), but not aggregate inside row-level (aggregate needs the full scan to complete first).

Each aggregate receives row-level evaluated values: COUNT(*) receives 1 for every row SUM receives 1 for returns, 0 for non-returns AVG receives amount for discounted orders, NULL for full-price (which AVG ignores) This pattern lets you calculate multiple different metrics in a single pass through the data.

COUNT is deceptively simple. Understanding its variations and NULL handling helps you write accurate data quality queries.

COUNT(*) seems simple, but understanding its nuances helps you write better queries. Let's explore what it actually does and when to use alternatives.

COUNT(*) counts rows, period. It doesn't look at column values. It doesn't care about NULLs. If a row exists in the result set, it gets counted.

Consider this table with some NULL values:

COUNT(*) returns 5 (all rows exist). COUNT(name) returns 4 (one NULL name). COUNT(email) returns 3 (two NULL emails). NULL represents missing values.

You might see COUNT(1) in legacy code or tutorials. It's functionally identical to COUNT(*) since both count rows. Use COUNT(*) for clarity because it's the standard convention and clearly expresses "count all rows."

Use COUNT(column) when you specifically need to count non-NULL values. Common use cases:

Which COUNT variant answers "how many rows have a value in the email column"?

COUNT variants have different performance characteristics depending on how the database processes them.

SELECT
  department,
  COUNT(___) AS total,
  COUNT(___) AS with_value
FROM employee_metrics
GROUP BY department

CASE WHEN inside SUM or AVG is essentially conditional aggregation: you decide, row by row, whether a value should contribute to the aggregate. This single pattern replaces many common reporting subqueries.

Predict how joins change your row count

Status column

Values like [pending, completed, cancelled] have cardinality 3.

User ID column

1 million unique users means cardinality of 1,000,000.

Country column

About ~200 unique values (number of countries).

Boolean column

Cardinality 2 (true/false) or 3 if NULLs exist.

Choose effective GROUP BY columns (low cardinality = fewer groups) Predict query performance (high cardinality = more memory for DISTINCT) Design efficient indexes (high cardinality columns make better indexes) Identify data quality issues (unexpected cardinality suggests problems)

Low cardinality columns like status, region, or category are ideal for GROUP BY. High cardinality columns like user_id or order_id are better for filtering or joining.

Which column would produce fewer groups in a GROUP BY?

SELECT
  region,
  ___(___ ___) AS unique_customers
FROM orders
GROUP BY region
___ unique_customers DESC

Cardinality awareness shapes how you write GROUP BY queries. Low-cardinality columns like status or region produce compact, fast results. High-cardinality columns like user_id or order_id produce row-per-entity results that are better suited to filtering or joining.

COUNT(DISTINCT) is the primary tool for measuring cardinality directly in SQL. It tells you exactly how many unique values exist, which is essential for data profiling and deduplication work.

Count unique values in massive datasets

COUNT(DISTINCT) is essential for measuring cardinality, answering questions like how many unique users, sessions, or products. But on massive datasets, it becomes a performance bottleneck. APPROX_DISTINCT solves this with probabilistic counting that's fast at any scale.

In a distributed system, COUNT(DISTINCT) requires shuffling data to ensure the same value is processed by the same worker, adding network overhead to an already expensive operation.

APPROX_DISTINCT uses HyperLogLog (HLL), a probabilistic algorithm that estimates cardinality using a fixed amount of memory regardless of input size. The key insight: instead of storing every value, it stores a compact "sketch" that can estimate the count.

The performance difference between COUNT(DISTINCT) and APPROX_DISTINCT grows dramatically with data size. Here's how they compare as cardinality increases:

Notice that COUNT(DISTINCT) memory grows linearly with cardinality, while APPROX_DISTINCT stays constant at approximately 12 KB (the HLL sketch size). The speedup column shows typical query time improvement.

Scale is the deciding factor: under 1 million rows, COUNT(DISTINCT) runs fast enough that there is no reason to sacrifice precision. For 1–100 million rows, switch to APPROX_DISTINCT when query latency is a constraint. Beyond 100 million rows, strongly prefer APPROX_DISTINCT unless exactness is a hard requirement. Real-time dashboards should almost always use APPROX_DISTINCT to keep response times below the perceptible threshold.

SELECT
  page_url,
  ___(___) AS unique_visitors
FROM page_views
GROUP BY page_url

APPROX_DISTINCT uses constant memory regardless of how many unique values exist, making it ideal for billion-row tables.

Use COUNT(DISTINCT) when precision matters, such as financial reporting or compliance metrics where every count must be exact.

Rank groups by their aggregate values

Aggregated results are often more useful when sorted. ORDER BY lets you arrange your groups by any column or aggregate value: highest revenue first, most recent activity on top, or alphabetically by name.

ORDER BY works with aggregate columns just like regular columns. Sort by metrics to surface top or bottom performers.

The result shows your top customers ranked by total spending. DESC means descending (highest first). Use ASC for ascending (lowest first), which is the default if you don't specify.

You can reference your aggregate columns by their alias in ORDER BY. This makes queries more readable:

Combining ORDER BY with LIMIT creates powerful reporting patterns for finding top or bottom performers.

Combine ORDER BY with LIMIT to get "top N" or "bottom N" results. This is one of the most common reporting patterns.

Understanding when ORDER BY executes explains why aggregate aliases work in sorting.

ORDER BY runs near the end, after aggregation is complete. This is why you can sort by aggregate values since they exist by this point.

SELECT
  region,
  SUM(amount) AS total_spend
FROM cloud_costs
GROUP BY region
___ SUM(amount) ___

ORDER BY on aggregated results is how raw group data becomes an actionable ranked list. Top-N reports, leaderboards, and worst-performer alerts all rely on this pattern.

Combining ORDER BY with LIMIT is one of the most efficient patterns in analytical SQL. The database can often short-circuit the sort once it has identified the top N values, rather than sorting the entire result set.

Estimate percentiles at warehouse scale

APPROX_PERCENTILE provides fast percentile calculations. It takes the column and the percentile as a decimal (0.5 for median, 0.95 for p95):

ARBITRARY is a specialized aggregate for when you need any value from a group, particularly when all values are identical.

ARBITRARY is an aggregate function that returns any value from a group. Unlike MIN, MAX, or AVG, which compute a specific result, ARBITRARY simply picks one value and returns it, and that value could be different between query executions.

This might sound useless at first. Why would you want a random value? The answer lies in performance and intent: when you genuinely don't care which value you get, ARBITRARY is dramatically faster than alternatives.

Denormalized data

A user's name appears on every order row.

One-to-one relationships

Each order has exactly one shipping address.

Sampling

You want any example from each category.

Metadata columns

Timestamps or IDs that are constant per group.

Without ARBITRARY, you'd get an error: "user_name must appear in GROUP BY or be used in an aggregate function." Using ARBITRARY tells the database "I know all values are the same, just give me one."

Watch how ARBITRARY processes rows. When a new group appears, it takes the first value encountered. Subsequent rows for that group are ignored since the value is already set.

ARBITRARY has O(1) time complexity per group. It stores one value and ignores subsequent values. Compare this to MIN or MAX, which must compare every value to find the extreme, an O(n) operation where n is the group size.

ARBITRARY runs in O(1): it stores the first value it encounters and ignores the rest. MIN and MAX are O(n): they must compare every value in the group to find the extreme. SUM, COUNT, and AVG also run in O(n), processing every row per group. On billion-row tables this constant-time vs linear distinction becomes significant.

SELECT
  endpoint,
  ___(latency, ___) AS median_ms
FROM api_calls
GROUP BY endpoint

The median (0.5 percentile) is more representative than AVG for skewed distributions like response times or salaries.

ARBITRARY returns any single value from a group in O(1) time, useful when you need a representative value but do not care which specific one.