SQL Querying and Aggregations

Analytical work is mostly aggregation: taking many rows and reducing them to a summary. This article covers the clauses that do that and, just as important, the order in which they apply.

The examples use a single staging.orders table:

The shape of a SELECT#

SELECT   customer_id, sum(amount) AS total_amountFROM     staging.ordersWHERE    status = 'completed'GROUP BY customer_idHAVING   total_amount > 1000ORDER BY total_amount DESCLIMIT    10;

Each clause has one job:

• SELECT — which columns and computed values to return
• FROM — which table to read
• WHERE — which rows to keep, before grouping
• GROUP BY — how to collapse rows into groups
• HAVING — which groups to keep, after grouping
• ORDER BY — how to sort the result
• LIMIT — how many rows to return

Clause evaluation order#

You write SELECT first, but the database applies the clauses in a different order:

FROM       pick the table   ↓WHERE      drop rows that fail the row condition   ↓GROUP BY   collapse remaining rows into groups   ↓HAVING     drop groups that fail the group condition   ↓SELECT     compute the output columns   ↓ORDER BY   sort the result   ↓LIMIT      cut to N rows

Keeping this order in mind explains most confusing query results. In particular, it explains why an alias defined in SELECT cannot be used in WHERE (the SELECT has not run yet), and why filtering a sum belongs in HAVING, not WHERE.

Filtering rows with WHERE#

WHERE keeps rows that match a condition. It runs before any grouping, so it can only see column values, not aggregates.

SELECT order_id, amountFROM staging.ordersWHERE status = 'completed' AND amount >= 100;

Common operators: =, !=, <, <=, >, >=, BETWEEN a AND b, IN (...), LIKE 'prefix%', IS NULL, IS NOT NULL. Combine conditions with AND and OR, and group them with parentheses.

Grouping and aggregating#

GROUP BY collapses all rows that share the grouped values into one group. Aggregate functions then produce one value per group.

SELECT    customer_id,    count()      AS order_count,    sum(amount)  AS total_amount,    avg(amount)  AS avg_amount,    min(amount)  AS smallest,    max(amount)  AS largestFROM staging.ordersWHERE status = 'completed'GROUP BY customer_id;

Result:

The core aggregates:

Every column in SELECT must either be in GROUP BY or wrapped in an aggregate. A bare column that is neither has no single value for the group.

WHERE filters rows, HAVING filters groups#

This is the distinction that trips people up most.

• WHERE runs before GROUP BY. It decides which rows enter the grouping.
• HAVING runs after GROUP BY. It decides which groups survive, and it can reference aggregates.

SELECT    customer_id,    sum(amount) AS total_amountFROM staging.ordersWHERE status = 'completed'        -- keep only completed ordersGROUP BY customer_idHAVING total_amount > 1000;        -- keep only customers over 1000

Trying to write WHERE sum(amount) > 1000 is an error: at WHERE time there is no group yet, so there is no sum.

Grouping by an expression#

The grouped value does not have to be a raw column. Grouping by a function of a column is how time-bucketed summaries are built:

SELECT    toStartOfMonth(created_at) AS month,    sum(amount)                AS revenueFROM staging.ordersWHERE status = 'completed'GROUP BY toStartOfMonth(created_at)ORDER BY month;

toStartOfMonth, toStartOfWeek, toStartOfDay, toStartOfHour, and toStartOfInterval(created_at, INTERVAL 30 MINUTE) all bucket a timestamp so that rows in the same period fall into the same group.

Conditional aggregates#

ClickHouse offers an -If form of most aggregates. It applies the aggregate only to rows where a condition is true, without a separate query or a CASE expression:

SELECT    customer_id,    count()                               AS orders,    countIf(amount >= 1000)               AS large_orders,    sumIf(amount, status = 'completed')   AS completed_revenue,    avgIf(amount, status = 'completed')   AS avg_completedFROM staging.ordersGROUP BY customer_id;

countIf, sumIf, avgIf, minIf, maxIf, and uniqIf all follow the same shape: the normal arguments, plus a final boolean condition. They are a concise way to compute several filtered metrics in one pass over the data.

A worked query#

"For each customer, how much have they spent on completed orders, keeping only customers above 1000, highest first":

SELECT    customer_id,    count()      AS orders,    sum(amount)  AS total_amount,    avg(amount)  AS avg_amountFROM staging.ordersWHERE status = 'completed'GROUP BY customer_idHAVING total_amount > 1000ORDER BY total_amount DESCLIMIT 100;

Reading it in evaluation order: read staging.orders, drop non-completed rows, group the rest by customer_id, compute the three aggregates per customer, drop customers at or below 1000, sort by total_amount descending, return the first 100.

Common mistakes#

Putting an aggregate condition in WHERE#

WHERE sum(amount) > 1000 fails. Filtering on an aggregate is HAVING.

Selecting a column that is not grouped or aggregated#

Every non-aggregated column in SELECT must appear in GROUP BY.

Using a SELECT alias in WHERE#

SELECT runs after WHERE, so the alias does not exist yet. Repeat the expression, or filter in a later stage.

Assuming count(col) equals count()#

count() counts rows; count(col) counts non-null values of that column.

SELECT * for an aggregation#

Aggregation queries should name exactly the grouped columns and aggregates they return.

Quick reference#

See also#