SQL
Conditional Aggregation with CASE
Perform multiple counts or sums based on different conditions within a single SQL query using `CASE` statements. Ideal for concise reporting and dashboards.
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users,
COUNT(CASE WHEN created_at >= '2023-01-01' THEN 1 ELSE NULL END) AS new_users_2023
FROM
users;
How it works: This query uses `CASE` statements within aggregate functions (`SUM`, `COUNT`) to perform conditional aggregations. It allows you to count or sum data based on different criteria in a single scan of the table, making reports more efficient and concise by avoiding multiple queries.