Open Ticket Load per Support Agent — SQL Joins Problem
Before assigning new tickets, the support manager wants to see how many tickets each agent is still working on — tickets whose status is 'open' or 'pending'.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #324
Problem statement
Before assigning new tickets, the support manager wants to see how many tickets each agent is still working on — tickets whose status is 'open' or 'pending'. Resolved and closed tickets are finished work and do not count, and unassigned tickets (agent_id NULL) belong to nobody.
Return every agent with the columns agent_id, agent_name and open_tickets; an agent with no open or pending ticket gets 0. Return the rows in any order.
Tables
Table: Agent
| Column | Type |
|---|---|
| agent_id | int |
| agent_name | varchar |
| team | varchar |
Primary key: agent_id.
One row per support agent.
Table: Ticket
| Column | Type |
|---|---|
| ticket_id | int |
| agent_id | int |
| status | enum(open, pending, resolved, closed) |
Primary key: ticket_id.
agent_id is the assigned agent, or NULL while the ticket sits in the unassigned queue.
Examples
Example 1
Agent
| agent_id | agent_name | team |
|---|---|---|
| 1 | Priya | Payments |
| 2 | Kabir | Payments |
| 3 | Sneha | Billing |
| 4 | Arjun | Billing |
Ticket
| ticket_id | agent_id | status |
|---|---|---|
| 501 | 1 | open |
| 502 | 1 | pending |
| 503 | 1 | resolved |
| 504 | 2 | closed |
| 505 | 3 | open |
| 506 | NULL | open |
| 507 | 2 | resolved |
Output
| agent_id | agent_name | open_tickets |
|---|---|---|
| 1 | Priya | 2 |
| 2 | Kabir | 0 |
| 3 | Sneha | 1 |
| 4 | Arjun | 0 |
How to solve Open Ticket Load per Support Agent
The answer has one row per agent, so the query starts from Agent and LEFT JOINs the tickets: an agent with no matching ticket still appears once, with NULL in every ticket column.
The subtle part is the status filter. Put t.status IN ('open', 'pending') in the WHERE clause and it runs after the join — on the NULL rows of agents without tickets, and on agents whose tickets are all resolved, the condition is not true and those agents vanish, so they never show their 0. Inside the ON clause the condition only decides which tickets match, and the agent row survives regardless.
COUNT(t.ticket_id) then counts the matched tickets and gives 0 for an agent whose only row is the NULL one — COUNT(*) would say 1. Unassigned tickets have agent_id NULL, which equals no agent, so they are ignored without extra work. A correlated subquery per agent, or a conditional SUM over an unfiltered LEFT JOIN wrapped in COALESCE, gives the same numbers; all three are one pass with an index on Ticket.agent_id.
Reference solution (MySQL)
SELECT a.agent_id, a.agent_name, COUNT(t.ticket_id) AS open_tickets
FROM Agent a
LEFT JOIN Ticket t
ON t.agent_id = a.agent_id AND t.status IN ('open', 'pending')
GROUP BY a.agent_id, a.agent_nameAnother way
SELECT a.agent_id, a.agent_name, (SELECT COUNT(*) FROM Ticket t WHERE t.agent_id = a.agent_id AND t.status IN ('open', 'pending')) AS open_tickets FROM Agent aAnother way
SELECT a.agent_id, a.agent_name,
COALESCE(SUM(CASE WHEN t.status IN ('open', 'pending') THEN 1 ELSE 0 END), 0) AS open_tickets
FROM Agent a LEFT JOIN Ticket t ON t.agent_id = a.agent_id
GROUP BY a.agent_id, a.agent_name← Urgent Support Tickets Waiting More Than a Day · Hosts That Hit CPU Saturation →