DEV Community

Cover image for SQL Pattern Series #20: The Filtered Aggregate Pattern
Baldwin Apps
Baldwin Apps

Posted on

SQL Pattern Series #20: The Filtered Aggregate Pattern

#beginners #sql #database #programming

SQL Pattern Series #20: The Filtered Aggregate Pattern

Sometimes you need multiple metrics from the same dataset.

For example:

  • total orders
  • completed orders
  • cancelled orders
  • completed revenue

A common beginner approach is writing a separate query for each metric.

The Filtered Aggregate Pattern lets you calculate them all in a single query.

The Pattern

Use an aggregate function together with a CASE expression.

SUM(
    CASE
        WHEN condition
        THEN value
        ELSE 0
    END
)

COUNT(
    CASE
        WHEN condition
        THEN 1
    END
)
Enter fullscreen mode Exit fullscreen mode

Common aggregate functions include:

SUM()
COUNT()
AVG()
MIN()
MAX()
Enter fullscreen mode Exit fullscreen mode

The idea is simple:

Aggregate only the rows you care about.

Example

Suppose we want several metrics for each customer.

SELECT
    customer_id,

    COUNT(*) AS total_orders,

    COUNT(
        CASE
            WHEN status = 'Completed'
            THEN 1
        END
    ) AS completed_orders,

    SUM(
        CASE
            WHEN status = 'Completed'
            THEN amount
            ELSE 0
        END
    ) AS completed_revenue,

    COUNT(
        CASE
            WHEN status = 'Cancelled'
            THEN 1
        END
    ) AS cancelled_orders

FROM Orders
GROUP BY customer_id;
Enter fullscreen mode Exit fullscreen mode

This query returns multiple business metrics from a single grouped result set.

Intermediate Note

When using COUNT(CASE...), many SQL developers omit the ELSE clause entirely.

Because COUNT() ignores NULL, this naturally counts only rows that satisfy the condition.

For example:

COUNT(
    CASE
        WHEN status = 'Completed'
        THEN 1
    END
)
Enter fullscreen mode Exit fullscreen mode

is equivalent to:

COUNT(
    CASE
        WHEN status = 'Completed'
        THEN 1
        ELSE NULL
    END
)
Enter fullscreen mode Exit fullscreen mode

This is why you'll often see:

COUNT(CASE WHEN status = 'Completed' THEN 1 END)
Enter fullscreen mode Exit fullscreen mode

in production SQL code.

Why This Pattern Matters

The Filtered Aggregate Pattern is one of the most practical reporting techniques in SQL.

It allows you to calculate multiple KPIs without repeatedly scanning the same table.

Common uses include:

  • Completed vs cancelled orders
  • Paid vs unpaid invoices
  • Active vs inactive users
  • Revenue by status
  • Success vs failure counts
  • Dashboard metrics

Instead of writing multiple queries, you can often calculate everything in a single grouped query.

Thinking in Buckets

A useful mental model is to think of each CASE expression as a bucket.

CASE
    WHEN status = 'Completed'
    THEN amount
    ELSE 0
END
Enter fullscreen mode Exit fullscreen mode

places revenue into the "Completed" bucket.

The aggregate function then totals everything in that bucket.

This makes complex reports much easier to reason about.

SQL Dialect Note

The Filtered Aggregate Pattern works across major SQL platforms including:

  • SQL Server
  • PostgreSQL
  • MySQL
  • MariaDB
  • Oracle

Some databases also support additional syntax such as FILTER(), but the CASE approach is widely portable.

When I Reach for This Pattern

I reach for this pattern whenever someone asks:

"Can we see multiple metrics in the same report?"

Instead of running separate queries, a filtered aggregate often produces everything needed in a single result set.

Key Takeaway

The Filtered Aggregate Pattern allows you to calculate multiple metrics from the same dataset by combining aggregate functions with conditional logic.

The fastest query is often the one that answers multiple questions in a single pass through the data

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

Top comments (0)