Ticket Breakdown Per Agent — SQL

Solving tips

To pivot categories into columns, aggregate a CASE expression: SUM(CASE WHEN cond THEN 1 ELSE 0 END).
COUNT(*) FILTER (WHERE cond) is the cleaner Postgres form of the same conditional count.
A boolean column can be counted directly with FILTER (WHERE flag) — no comparison needed.

Given a support ticket log, produce one row per agent that breaks their tickets down by priority and shows how many were resolved.

Schema

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  agent     TEXT NOT NULL,
  priority  TEXT NOT NULL,     -- 'high' or 'low'
  resolved  BOOLEAN NOT NULL
);

Sample data:

ticket_id	agent	priority	resolved
1	Ann	high	true
2	Ann	low	false
3	Ann	high	false
4	Bob	low	true
5	Bob	low	true
6	Cara	high	true

Task

For each agent, return:

agent
high_count — number of high priority tickets
low_count — number of low priority tickets
resolved_count — number of resolved tickets

Order the result by agent ascending.

Expected output

agent	high_count	low_count	resolved_count
Ann	2	1	1
Bob	0	2	2
Cara	1	0	1

Approach

Group by agent and turn each category into its own column with conditional aggregation. The portable form sums a CASE expression that yields 1 when the condition holds and 0 otherwise. Postgres also offers the aggregate FILTER clause, which reads more cleanly; both produce the same numbers. Because the totals are per agent in a single row, this is a one-pass pivot.

Query

SELECT
  agent,
  SUM(CASE WHEN priority = 'high' THEN 1 ELSE 0 END) AS high_count,
  SUM(CASE WHEN priority = 'low'  THEN 1 ELSE 0 END) AS low_count,
  SUM(CASE WHEN resolved THEN 1 ELSE 0 END)          AS resolved_count
FROM tickets
GROUP BY agent
ORDER BY agent ASC;

Equivalent using the FILTER clause (Postgres, SQLite 3.30+):

SELECT
  agent,
  COUNT(*) FILTER (WHERE priority = 'high') AS high_count,
  COUNT(*) FILTER (WHERE priority = 'low')  AS low_count,
  COUNT(*) FILTER (WHERE resolved)          AS resolved_count
FROM tickets
GROUP BY agent
ORDER BY agent ASC;

Walkthrough

Ann (ids 1, 2, 3): priorities high, low, high, so high_count = 2 and low_count = 1. Only ticket 1 is resolved, so resolved_count = 1.
Bob (ids 4, 5): both low, so high_count = 0 and low_count = 2. Both resolved, so resolved_count = 2.
Cara (id 6): one high ticket, resolved. high_count = 1, low_count = 0, resolved_count = 1.
ORDER BY agent sorts Ann, Bob, Cara.

Complexity & notes

Single grouped pass, O(n); the conditional counters are evaluated row by row within each group.
With SUM(CASE ... THEN 1 ELSE 0 END) the ELSE 0 matters: SUM(CASE WHEN cond THEN 1 END) returns NULL for a group with no matching rows instead of 0, so wrap it in COALESCE or keep the ELSE 0. The FILTER and CASE-with-ELSE forms both return 0, as Bob’s high_count and Cara’s low_count show.
resolved is already boolean, so FILTER (WHERE resolved) needs no = true. In the CASE form, WHEN resolved THEN 1 works for the same reason.
If your engine lacks FILTER (e.g. MySQL), use the SUM(CASE ...) form, which is universally supported.