SQL Conditional Aggregation: Beyond Basic GROUP BY

You've mastered COUNT(), SUM(), and GROUP BY. But what if you need to count only certain rows, or create a pivot table in a single query?

Conditional aggregation combines CASE WHEN with aggregate functions to answer complex questions without multiple queries or subqueries.

The Basic Pattern

Instead of:

-- Two separate queries
SELECT COUNT(*) FROM orders WHERE status = 'completed';
SELECT COUNT(*) FROM orders WHERE status = 'pending';

Use one query:

SELECT 
  COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending
FROM orders;

Interactive Example: Order Status Report

One query, one result row, all the answers!

SUM with Conditions

Calculate revenue by status:

Note: Use ELSE 0 with SUM() to avoid NULL issues.

Creating Pivot Tables

Transform rows into columns - a classic pivot table:

This turns vertical month data into horizontal columns - perfect for reports!

Calculating Percentages

Combine conditional count with total count:

Conditional AVG and MIN/MAX

Works with any aggregate:

Combining with GROUP BY

Create detailed breakdowns per group:

Boolean Flags with CASE

Create flags for each row, then aggregate:

-- Flag orders, then summarize
SELECT 
  SUM(is_high_value) as high_value_count,
  SUM(is_new_customer) as new_customer_orders
FROM (
  SELECT 
    *,
    CASE WHEN amount > 500 THEN 1 ELSE 0 END as is_high_value,
    CASE WHEN customer_age_days < 30 THEN 1 ELSE 0 END as is_new_customer
  FROM orders
);

Why Conditional Aggregation Matters in Real Reporting

This pattern shows up constantly because reporting questions rarely ask for just one number. They ask for a compact summary with multiple related metrics side by side:

• total orders
• completed orders
• cancelled orders
• completion rate
• revenue from completed orders

Without conditional aggregation, teams often write several separate queries or push the logic into application code. That works, but it creates more round-trips, more duplication, and more chances for the numbers to drift out of sync.

Conditional aggregation keeps those metrics in one place and forces them to be computed against the same grouped dataset.

COUNT vs SUM with CASE

These two patterns look similar, but they behave differently.

Count matching rows

COUNT(CASE WHEN status = 'completed' THEN 1 END)

This works because COUNT ignores NULL.

Sum matching values

SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)

This works because SUM adds the returned values together, so you usually want ELSE 0.

That distinction matters. A lot of conditional aggregation bugs come from mixing up "count rows" and "sum values."

Conditional Aggregation vs Window Functions

These patterns are related but solve different problems.

• Conditional aggregation is best when you want one row per group with many metrics.
• Window functions are best when you want to keep every row and add analytical context to each row.

Example:

• "How many completed vs pending orders does each customer have?" -> conditional aggregation
• "What rank is each order inside the customer timeline?" -> window function

In real dashboards, the two often work together: conditional aggregation for the KPI block, window functions for the drill-down table.

A Practical Workflow for Building KPI Queries

When the reporting query starts getting messy, build it in this order:

1. Confirm the base dataset and filters.
2. Decide the grouping grain: one row per customer, month, product, or region.
3. Add one conditional metric at a time.
4. Check totals and percentages against a small hand-verified sample.
5. Only then add more columns, formatting, or pivot-like output.

That process matters because KPI queries are easy to make syntactically valid but logically inconsistent.

Quick Reference

Goal	Pattern
Count if condition	COUNT(CASE WHEN cond THEN 1 END)
Sum if condition	SUM(CASE WHEN cond THEN value ELSE 0 END)
Average if condition	AVG(CASE WHEN cond THEN value END)
Percentage	COUNT(CASE...) * 100.0 / COUNT(*)
Pivot table	SUM(CASE WHEN col = 'X' ...) per column

Common Mistakes

1. Forgetting ELSE in SUM: Without ELSE 0, you get NULL which propagates incorrectly.

2. Using ELSE with COUNT: Don't need it - COUNT(NULL) returns 0 automatically.

3. Complex conditions: Use AND/OR inside CASE:

   COUNT(CASE WHEN status = 'completed' AND amount > 100 THEN 1 END)
   

PostgreSQL FILTER Clause

PostgreSQL offers a cleaner syntax:

-- PostgreSQL only
SELECT 
  COUNT(*) FILTER (WHERE status = 'completed') as completed,
  SUM(amount) FILTER (WHERE status = 'pending') as pending_total
FROM orders;

This is equivalent to the CASE WHEN approach but more readable.

Related Articles

• SQL CASE Statements Explained for the branching logic that powers conditional metrics.
• Understanding SQL Window Functions: A Visual Guide for row-preserving analytical queries that often complement grouped KPI reports.
• Mastering CTEs: Writing Cleaner, Better SQL for structuring larger reporting queries into readable stages.

Conclusion

Conditional aggregation is a report-building superpower:

• Turn multiple queries into one
• Create pivot tables without complex joins
• Calculate percentages and ratios
• Build dashboards with a single SQL statement

The pattern is simple: put CASE WHEN inside any aggregate function. Master this technique and you'll write cleaner, faster SQL for any analytical reporting task.