How to optimize this sql query?

Question

SQL

SELECT COUNT(*) as `all_ct`, (SELECT COUNT(*) FROM `requests` WHERE `statut` = 'new') as `ct_new`, 
(SELECT COUNT(*) FROM `requests` t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'analyze' and t2.id_client IS NOT NULL) as `ct_analyze`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'analyzed' and t2.id_client IS NOT NULL) as`ct_analyzed`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'sale' and t2.id_client IS NOT NULL) as `ct_sale`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'meeting' and t2.id_client IS NOT NULL) as `ct_meeting`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'portfolio' and t2.id_client IS NOT NULL) as`ct_portfolio`,
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'canceled' and t2.id_client IS NOT NULL) as `ct_canceled`  
FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t2.id_client IS NOT NULL

it works for a long time, if there is a client, then we consider requests, there can only be a request without a client, which is why a verification condition is needed

Answer 1

You can try to replace a bunch of requests of the same type with one using grouping, something like this:

SELECT SUM(CASE WHEN t1.`statut` = 'analyze'   
THEN 1 ELSE 0 END) as `ct_analyze`,
       SUM(CASE WHEN t1.`statut` = 'analyzed'   
THEN 1 ELSE 0 END) as `ct_analyzed`,
... ДРУГИЕ ПОЛЯ... 
   FROM `requests` t1 
   LEFT JOIN clients t2 
          ON t2.id_client = t1.id_client  
WHERE tnd t2.id_client IS NOT NULL
GROUP BY t1.id_client