如何从同一个表中简化计数查询

Question

hi i am trying to reduce the code how can i simplify these lines. please suggest edit if question not reach standards.

$invCount = $conn->query("SELECT 
(SELECT COUNT(*) FROM r_job_invitations WHERE id_job='".$_POST['JobId']."' and inv_st=1) as clginvcount,
(SELECT COUNT(*) FROM r_job_invitations WHERE id_job='".$_POST['JobId']."' and inv_res=1) as clgaccptdcount,
(SELECT COUNT(*) FROM r_job_invitations WHERE id_job='".$_POST['JobId']."' and inv_res=2) as clgrejectedcount,
(SELECT COUNT(*) FROM r_job_invitations WHERE id_job='".$_POST['JobId']."' and inv_res=0) as clgnoresponsecount");
$invCountRes = $invCount->fetch_assoc();

Answer 1

If you have only values 0,1,2 in coumn inv_res you can use this query and also please first validate your user input:

$jobId = mysqli_real_escape_string($_POST['JobId']);
SELECT 
    count(*), inv_res 
FROM  r_job_invitations 
WHERE 
    id_job ='".$jobId."' 
GROUP BY inv_res

If you have more values, use following

SELECT 
    count(*), inv_res 
FROM  r_job_invitations 
WHERE 
    id_job ='".$jobId."' AND 
    inv_res IN(0,1,2)
GROUP BY inv_res