Filtered Aggregations with COUNT(CASE WHEN) in SQL

Question

From a `tickets` table, return a single summary row showing `total_tickets`, `resolved_tickets`, and `escalated_tickets` using conditional aggregation.

Accepted Answer

Optimal Solution:
SELECT 
  COUNT(*) AS total_tickets,
  COUNT(CASE WHEN status = 'Resolved' THEN 1 END) AS resolved_tickets,
  COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS escalated_tickets
FROM tickets;

Technical Explanation:
When the `CASE` condition is false, it returns `NULL`. Since `COUNT(column)` ignores `NULL` values, only rows matching the condition are counted.

ticket_id	status	priority
1	Resolved	High
2	Escalated	Critical
3	Resolved	Low
4	Open	Medium