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.

wanted_severity
conditional_counts.sql
Replay: real traced execution (multi-file project)
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 ('high')) 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 ('low')) 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;

  1. tables ← 1 row

    1CREATE TABLE tickets (ticket_id INTEGER, severity TEXT, status TEXT);2INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');
    values this step1 rowtables

  2. tickets ← 5 rows

    1CREATE TABLE tickets (ticket_id INTEGER, severity TEXT, status TEXT);2INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');3WITH params(wanted_severity) AS (VALUES ('high')) 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;
    values this step5 rowstickets

  3. result ← 1 row

    2INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');3WITH params(wanted_severity) AS (VALUES ('high')) 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;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE tickets (ticket_id INTEGER, severity TEXT, status TEXT);2INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');
    values this step1 rowtables

  2. tickets ← 5 rows

    1CREATE TABLE tickets (ticket_id INTEGER, severity TEXT, status TEXT);2INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');3WITH params(wanted_severity) AS (VALUES ('low')) 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;
    values this step5 rowstickets

  3. result ← 1 row

    2INSERT INTO tickets VALUES (1, 'high', 'open'), (2, 'high', 'closed'), (3, 'high', 'open'), (4, 'low', 'closed'), (5, 'low', 'open');3WITH params(wanted_severity) AS (VALUES ('low')) 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;
    values this step1 rowresult
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.