I am getting a “subquery returning multiple rows” error. I want to return multiple rows however

I want to have a subquery which returns multiple rows in MySQL. I used the IN function as well. However i am getting a “subquery returning multiple rows” error. I have attached an image of the tables used and the desired output Here is my SQL code:

Select t2.STUID, t3.UnitCmd, //main query

(Select count(*) as 'FullDutyCount' from stormtroopers_officer A1 inner join st_officer_assign A2 ON A1.STID = A2.STID where A2.STUID IN('STU-1','STU-2','STU-3') AND A1.DutyStatus = 'Full Duty' group by A1.Dutystatus,A2.STUID), //subquery1

(Select count(*) as 'WoundedCount' from stormtroopers_officer A1 inner join st_officer_assign A2 ON A1.STID = A2.STID where A2.STUID IN('STU-1','STU-2','STU-3') AND A1.DutyStatus = 'Wounded' group by A1.Dutystatus,A2.STUID),//subquery2

(Select count(*) as 'KilledCount' from stormtroopers_officer A1 inner join st_officer_assign A2 ON A1.STID = A2.STID where A2.STUID IN('STU-1','STU-2','STU-3') AND A1.DutyStatus = 'Killed' group by A1.Dutystatus,A2.STUID) //subquery 3

FROM stormtroopers_officer t1 inner join st_officer_assign t2 ON t1.STID = t2.STID
inner join stormtrooper_unit t3 ON t2.STUID = t3.STUID where t2.STUID IN ('STU-1','STU-2','STU-3') group by t1.Dutystatus,t2.STUID;

Answer

You seem to want conditional aggregation:

select
    su.stuid,
    su.unitCmd unitHQ,
    sum(so.dutyStatus = 'Full Duty') fullDutyCount,
    sum(so.dutyStatus = 'Wounded'  ) woundedCount,
    sum(so.dutyStatus = 'Killed'   ) killedCount
from st_officier_assign soa
inner join stormtrooper_unit su on su.stuid = soa.stuid
inner join stormtrooper_officer so on so.stid = so.stid
where su.stuid in ('STU-1','STU-2','STU-3')
group by su.stuid, su.unitCmd