Sonali Sonali - 1 month ago 16
JSON Question

Multiple COUNT and GROUP BY in a single Statement

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:

enter image description here

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

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
Comments