CASE with aggregate functions in SQL

Introduction

Use CASE with aggregate functions to count or sum values conditionally in a group of data.

Counting how many orders are above a certain amount in each city.

Summing sales only for a specific product category.

Calculating average scores for students who passed versus those who failed.

Finding the total revenue from online versus in-store sales.

Syntax

SQL

SELECT
  column1,
  AGGREGATE_FUNCTION(CASE WHEN condition THEN value ELSE alternative END) AS alias
FROM
  table_name
GROUP BY
  column1;

AGGREGATE_FUNCTION can be SUM, COUNT, AVG, MAX, MIN, etc.

CASE lets you choose values based on conditions before aggregation.

Examples

Counts how many female employees are in each department.

SQL

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

Calculates average rating only for ratings 4 or higher per category.

SQL

SELECT
  product_category,
  AVG(CASE WHEN rating >= 4 THEN rating ELSE NULL END) AS avg_high_rating
FROM reviews
GROUP BY product_category;

Counts orders over 100 in each city.

SQL

SELECT
  city,
  COUNT(CASE WHEN order_total > 100 THEN 1 ELSE NULL END) AS big_orders
FROM orders
GROUP BY city;

Sample Program

This query sums and counts sales amounts greater than 100 for each region.

SQL

CREATE TABLE sales (
  region VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
('North', 120),
('North', 80),
('South', 150),
('South', 50),
('East', 200),
('East', 90);

SELECT
  region,
  SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS high_sales_total,
  COUNT(CASE WHEN amount > 100 THEN 1 ELSE NULL END) AS high_sales_count
FROM sales
GROUP BY region;

Important Notes

Using ELSE 0 in SUM ensures non-matching rows add zero instead of NULL.

For COUNT, use ELSE NULL to count only matching rows.

CASE inside aggregate helps filter data without needing WHERE.

Summary

CASE lets you pick values conditionally inside aggregate functions.

Use SUM, COUNT, AVG with CASE to get conditional totals or counts.

This technique helps analyze data by groups with specific conditions.