GROUP BY Aggregations

From Rows to Summaries

Raw transactional data is too granular for decision-making. A fact table with 10 million order line items does not tell a VP of Sales which region is performing well. Aggregation transforms row-level detail into summary-level insight by collapsing groups of rows into single representative values.

SQL provides aggregation through two mechanisms working together: the GROUP BY clause, which defines the groups, and aggregate functions, which compute a single value per group.

The Logical Execution Order

Understanding when aggregation happens relative to other clauses is essential for writing correct queries. SQL does not execute clauses in the order they appear. The logical order is:

1. FROM — identify the source tables
2. JOIN — combine tables
3. WHERE — filter rows before grouping
4. GROUP BY — partition rows into groups
5. HAVING — filter groups after aggregation
6. SELECT — compute expressions and aggregates
7. ORDER BY — sort the final result
8. LIMIT — restrict the number of rows returned

The critical insight: WHERE filters rows before grouping, and HAVING filters groups after aggregation. This distinction is the source of many bugs and is covered in detail in the next topic.

Basic Syntax

SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_revenue
FROM orders
GROUP BY region;

This query partitions all order rows by region, then computes the count and sum within each partition. The result has one row per region.

What GROUP BY Does Conceptually

Imagine sorting a deck of cards by suit. You create four piles — one per suit. Each pile is a group. An aggregate function then operates on each pile independently: count the cards, sum the values, find the highest card. GROUP BY is the sorting step. The aggregate functions are the per-pile computations.

The Core Five

Every aggregate function takes a multiset of values (one per row in the group) and returns a single value.

COUNT

COUNT(*) counts all rows in the group, including rows with NULL values. COUNT(column) counts only rows where column is not NULL.

SELECT region,
    COUNT(*) AS total_orders,
    COUNT(customer_id) AS orders_with_customer
FROM orders
GROUP BY region;

If some orders have a NULL customer_id, total_orders will be larger than orders_with_customer. This difference is often meaningful — it reveals data quality issues.

SUM

SUM(column) adds all non-NULL values in the group. If all values are NULL, the result is NULL (not zero).

SELECT product_category, SUM(quantity) AS total_units_sold
FROM order_items
GROUP BY product_category;

AVG

AVG(column) computes the arithmetic mean of non-NULL values. It ignores NULLs entirely — they are not treated as zero.

SELECT region, AVG(amount) AS avg_order_value
FROM orders
GROUP BY region;

A common mistake is assuming AVG treats NULL as zero. It does not. If you need to treat NULL as zero, use AVG(COALESCE(amount, 0)).

MIN and MAX

MIN and MAX return the smallest and largest values in the group. They work on numbers, dates, and strings.

SELECT region,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order
FROM orders
GROUP BY region;

COUNT(DISTINCT column)

COUNT(DISTINCT column) counts the number of unique non-NULL values in the group. This is one of the most common patterns in analytical SQL.

SELECT region, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY region;

Without DISTINCT, you would count total orders per region. With DISTINCT, you count how many different customers placed those orders. The difference between the two reveals purchase frequency.

Combining Multiple Aggregates

A single GROUP BY can compute multiple aggregates at once:

SELECT
    region,
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    MIN(amount) AS min_order,
    MAX(amount) AS max_order
FROM orders
GROUP BY region;

This single query produces a complete regional performance summary. Each aggregate operates independently over the same set of groups.

Multi-dimensional Grouping

GROUP BY accepts multiple columns. The result has one row per unique combination of the grouping columns.

SELECT region, product_category, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY region, product_category;

This query produces a matrix of region × product_category. Each cell contains the order count and revenue for that specific combination.

Why Multiple Dimensions Matter

Single-dimension grouping ("revenue by region") is useful but coarse. Multi-dimension grouping ("revenue by region and category") reveals patterns that single-dimension views hide. A region might have high overall revenue driven entirely by one category. Multi-dimension grouping exposes that concentration.

The Grain of the Result

The grouping columns define the grain of the result — the level of detail. GROUP BY region produces region-level grain. GROUP BY region, product_category produces region-category-level grain. Adding more grouping columns increases the grain (more rows, less aggregation per row).

Practical Example: Monthly Revenue by Region

SELECT
    region,
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY region, DATE_TRUNC('month', order_date)
ORDER BY region, month;

DATE_TRUNC('month', order_date) truncates each date to the first of its month, creating a month-level grouping key. The result is a time series of revenue per region.

Columns in SELECT Must Be in GROUP BY (or Aggregated)

SQL requires that every non-aggregated column in the SELECT clause appear in the GROUP BY clause. This is not arbitrary — it is a correctness requirement. If you select region without grouping by it, the database cannot know which region's value to show for a group that spans multiple regions.

-- This query is invalid in standard SQL
SELECT region, product_category, SUM(amount)
FROM orders
GROUP BY region;
-- Error: product_category is not in GROUP BY and not aggregated

MySQL with ONLY_FULL_GROUP_BY disabled will execute this query and return an arbitrary product_category value, which is a silent correctness bug. Always include all non-aggregated SELECT columns in the GROUP BY.

Pattern 1: Ratio Metrics

Combine aggregates to compute ratios:

SELECT
    region,
    COUNT(DISTINCT customer_id) * 1.0 / COUNT(*) AS avg_orders_per_customer
FROM orders
GROUP BY region;

The * 1.0 forces floating-point division. Without it, integer division truncates the decimal.

Pattern 2: Contribution to Total

Use a window function or subquery to compute each group's share of the overall total:

SELECT
    region,
    SUM(amount) AS region_revenue,
    SUM(amount) * 100.0 / SUM(SUM(amount)) OVER () AS pct_of_total
FROM orders
GROUP BY region;

SUM(SUM(amount)) OVER () computes the grand total across all groups. The inner SUM(amount) is the per-group aggregate; the outer SUM(...) OVER () is a window function that sums those per-group values.

Pattern 3: Conditional Aggregation

Use CASE inside an aggregate to compute conditional metrics:

SELECT
    region,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned_orders,
    SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS return_rate
FROM orders
GROUP BY region;

This pattern — SUM(CASE WHEN condition THEN 1 ELSE 0 END) — is the SQL equivalent of a filtered count. It lets you compute multiple conditional metrics in a single pass over the data.

Pattern 4: Handling NULL in Aggregates

SELECT
    region,
    COUNT(*) AS total_orders,
    COUNT(customer_id) AS orders_with_customer,
    COUNT(*) - COUNT(customer_id) AS orders_missing_customer
FROM orders
GROUP BY region;

The difference between COUNT(*) and COUNT(customer_id) reveals how many rows have NULL in customer_id. This is a data quality audit pattern.

When to Aggregate in SQL vs Application Code

Aggregate in SQL when:

• The data volume is large (reduce data transfer)
• The aggregation is part of a larger query (joins, filters)
• You need the database's optimization (indexes, parallel execution)

Aggregate in application code when:

• The aggregation logic is too complex for SQL
• You need to combine data from multiple sources
• The data volume is small and already in memory

For analytical work on large datasets, SQL aggregation is almost always the right choice.