Multiple conditional counts in a single Laravel SELECT query

Question

I am trying to count tickets based on different where conditions . I am using four different queries on the same model. Can I execute all of the calculations using just one query?

$openTickets = Ticket::where('status',1)->count();
$pending = Ticket::where('status',2)->count();
$unAssigned = Ticket::where('agent_id',null)->count();
$unResolved = Ticket::whereNotIn('status',[3,4])->count();

Answer 1

Ticket::selectRaw('COUNT(CASE WHEN status = 1 THEN 1 END) AS open_tickets')
      ->selectRaw('COUNT(CASE WHEN status = 2 THEN 1 END) AS pending_tickets')
      ->selectRaw('COUNT(CASE WHEN agent_id IS NULL THEN 1 END) AS unassigned_tickets')
      ->selectRaw('COUNT(CASE WHEN status NOT IN (3,4) THEN 1 END) AS unresolved_tickets')
      ->first();

You can of course resolve the multiple queries with this query. We can use conditional cases and count.