case counts

gistfile1.txt

<?php

$caseStatuses = (array) \Civi\Api4\CiviCase::getFields()
  ->setLoadOptions(['id','name','label'])
  ->setAction('')
  ->addWhere('name', '=', 'status_id')
  ->addSelect('options')
  ->execute()
  ->first();

foreach ($caseStatuses['options'] as $status) {
  $query[] = "SUM(cc.status_id=${status['id']}) AS \"${status['label']}\"";
}

$query = "SELECT ct.id, ct.title, " . implode(", ", $query) .
  " FROM civicrm_case_type ct " .
  " LEFT JOIN civicrm_case cc ON cc.case_type_id=ct.id AND cc.is_deleted=0 " .
  " GROUP BY ct.id ORDER BY ct.title";
print_r($query);

/*
Sample output:
SELECT ct.id, ct.title, SUM(cc.status_id=1) AS "Ongoing", SUM(cc.status_id=2) AS "Resolved", 
SUM(cc.status_id=3) AS "Urgent" 
FROM civicrm_case_type ct  
LEFT JOIN civicrm_case cc ON cc.case_type_id=ct.id AND cc.is_deleted=0  
GROUP BY ct.id 
ORDER BY ct.title
*/