Conditional aggregation pattern in SQL

Introduction

Conditional aggregation helps you count or sum values based on specific conditions in one query. It makes data summaries easier and faster.

Counting how many orders were placed by each customer in different regions.

Summing sales amounts only for a certain product category.

Finding how many employees joined in each year from a list of employees.

Calculating total revenue from online vs. in-store sales in one report.

Syntax

SQL

SELECT
  column1,
  SUM(CASE WHEN condition THEN value ELSE 0 END) AS alias_name
FROM
  table_name
GROUP BY
  column1;

The CASE statement checks a condition for each row and returns a value if true, else another value.

SUM or COUNT aggregates the results of the CASE for each group.

Examples

Counts how many female employees are in each department.

SQL

SELECT
  department,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count
FROM employees
GROUP BY department;

Sums sales amounts for each product category only for sales in 2024.

SQL

SELECT
  product_category,
  SUM(CASE WHEN sale_date >= '2024-01-01' THEN amount ELSE 0 END) AS sales_2024
FROM sales
GROUP BY product_category;

Sample Program

This query sums sales amounts for each customer separately for North and South regions.

SQL

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  region VARCHAR(20),
  amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 101, 'North', 100.00),
(2, 102, 'South', 150.00),
(3, 101, 'North', 200.00),
(4, 103, 'East', 50.00),
(5, 102, 'South', 100.00);

SELECT
  customer_id,
  SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS north_sales,
  SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS south_sales
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Important Notes

If the CASE condition is false and no ELSE is given, it returns NULL, which SUM ignores.

You can use COUNT with CASE to count rows matching a condition by returning 1 or NULL.

Grouping is necessary to aggregate data by categories or groups.

Summary

Conditional aggregation uses CASE inside aggregate functions to summarize data based on conditions.

It helps create multiple summaries in one query without multiple passes over data.

Commonly used with SUM and COUNT to filter what is counted or summed.