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
);

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.

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.