Reporting Queries

Conditional Aggregates

Count Statuses

A CASE expression inside SUM counts rows that match each condition, producing a compact status report.

Program

Play the script to choose a severity and compare open versus closed ticket counts.

conditional_counts.sql 
CREATE TABLE tickets (ticket_id INTEGER, severity TEXT, status TEXT);
INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');
WITH params(wanted_severity) AS (VALUES ()) SELECT severity, SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_count, SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) AS closed_count FROM tickets WHERE severity = (SELECT wanted_severity FROM params) GROUP BY severity;

CREATE TABLE tickets (ticket_id INTEGER, severity TEXT, status TEXT);
INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');
WITH params(wanted_severity) AS (VALUES ()) SELECT severity, SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_count, SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) AS closed_count FROM tickets WHERE severity = (SELECT wanted_severity FROM params) GROUP BY severity;
conditional aggregate `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` counts only rows matching the condition.
CASE `CASE` turns each row into `1` or `0` before the sum.
compact report One grouped row can show several related counts.