CASE with aggregates

Use CASE inside an aggregate.

CASE with aggregates

You can use CASE inside an aggregate for conditional counting. SUM(CASE WHEN ... THEN 1 ELSE 0 END).

SELECT category,
  SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;

This query gives the count of in-stock products per category.

category, SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS … is selected from products. Rows are grouped by category; aggregate functions are computed per group.

CASE with aggregates — details

You produce 1/0 with CASE and sum them. This pattern is common for conditional counting.

For example, SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) gives the number of in-stock products per category in a single query.

Exercise CASE with aggregates

Count in-stock products per category with SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END).

Show example solution

SELECT category,
  SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;

Test yourself

Add SUM

SELECT category,
  ____(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;

Show answer

SELECT category,
  SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;

CASE with aggregates: match the terms

SUM(CASE)

conditional count
WHEN

condition
THEN 1

count
ELSE 0

skip
What does conditional counting use?
- MIN + HAVING
- COUNT + WHERE
- AVG + GROUP BY
- SUM + CASE
Show answer

SUM + CASE — SUM and CASE do conditional counting.

Next lesson: Summary - CASE

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies