Part 107: Conditional Aggregation: Combining SUM & CASE

Pivoting rows into columns and calculating specific subset totals in a single pass.

1. Syntax

SELECT 
  department,
  COUNT(*) AS total_staff,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN is_remote = TRUE THEN salary ELSE 0 END) AS remote_payroll
FROM employees
GROUP BY department;

Key Takeaways

• SUM(CASE WHEN condition THEN 1 ELSE 0 END) is one of the most useful patterns in analytical SQL!

In Part 108, we explore Null Handling Functions: COALESCE, IFNULL, and NULLIF!