Conditional Aggregation

How can we count active and inactive employees per department side-by-side?

Advanced⏱ 3 Minutes⭐ 1870 XP

🤔 THE QUESTION

How can we count active and inactive employees per department side-by-side?

💡 WHAT IS IT?

Combining SUM(CASE WHEN ... THEN 1 ELSE 0 END) with GROUP BY pivots categorical statuses into distinct columns.

🎯 WHAT IS IT USED FOR?

Executive matrix dashboards, headcount status breakdowns, multi-state summaries.

💻 EXAMPLE

SELECT
department_id,
SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN status = 'Inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM employees
GROUP BY department_id;

🎯 Mission Objectives

Practice typing production-grade SQL code for Conditional Aggregation.

✓ Pivoted conditional sums
✓ Active vs Inactive matrix
✓ GROUP BY with CASE
✓ Executive status reporting

🚀 Start Mission

← Prev (Missing Sequence Detection)Next Lesson (Percentage by Group) →