select count(if(a.sex=1,1,NULL)) male, count(if(a.sex=0,1,NULL)) female, a.att_isstatus, if(att_isstatus=0,'Inside','Outing') att_status from hostellog c inner join stu a on c.uid=a.uid inner join (select prm,val from type where grp='sex') b on a.sex=b.val where a.sex!='' group by a.att_isstatus failed: