单个Statement中有多个COUNT和GROUP BY

Question

I am using DISTINCT, LEFT JOIN, COUNT and GROUP BY in single statement, like this:

SELECT distinct r.sid as sid, s.name as sname, s.image as simage, 
COUNT(r.sid) as scount FROM batch_request r LEFT JOIN student_info s ON s.id = r.sid 
WHERE r.tid='22' group by r.sid

Encoded JSON Looks like this:

{ "students":
[
{
"sid":"1",
"sname":"Sonali Kohli",
"simage":"22",
"scount":"3",
"sconfirmed":null,
"sdeclined":null
},
{
"sid":"2",
"sname":"Sona Ali Khan",
"simage":"22",
"scount":"3",
"sconfirmed":null,
"sdeclined":null
}
],"success":1
}

Table:

As you can see in above image, I have total 6 records in a table (3 for sid = 1 and 3 for sid = 2)

Status for sid 1 records (2 Confirmed [where value is 1] and 1 Declined [where value is 2]), in a same way Status for sid 2 records (1 Confirmed [where value is 1] and 2 Declined [where value is 2])

Same thing I want to get through my QUERY which I have posted above to encode data into JSON, as you can see, still I am getting null for both the JSON objects (i.e: Confirmed and Declined)

QUESTION 1: What should be the values of sconfirmed and sdeclined for both the objects of JSON ?

ANSWER 1: In case of sid = 1 (sconfirmed = 2 and sdeclined = 1) and for sid = 2 (sconfirmed = 1 and sdeclined = 2)

QUESTION 2: What is sconfirmed and sdeclined in a database table ?

ANSWER 2: sconfirmed is just the count of records where status is 1 for a particular sid and sdeclined is the count of records where status is 2 for a particular sid

Answer 1

Try something like this:

SELECT distinct r.sid as sid, s.name as sname, s.image as simage, COUNT(r.sid) as scount, 
SUM(CASE r.status WHEN 1 THEN 1 ELSE 0 END) as sconfirmed, 
SUM(CASE r.status WHEN 2 THEN 1 ELSE 0 END) as sdeclined, 
SUM(CASE r.status WHEN 0 THEN 1 ELSE 0 END) as spending  
FROM batch_request r LEFT JOIN student_info s ON s.id = r.sid 
WHERE r.tid='22' 
GROUP BY r.sid