select InstitutionID=LEFT(InstitutionID,9), Irregularities_Type=sum(CASE WHEN Irregularities_Type>0 THEN 1 ELSE 0 END)
,Irregularities_Type2=sum(CASE WHEN Irregularities_Type2>0 THEN 1 ELSE 0 END),Irregularities_Type3=sum(CASE WHEN Irregularities_Type3>0 THEN 1 ELSE 0 END)
from
(
select IrregularitiesList.Irregularities_Type, IrregularitiesList.Irregularities_Type2,IrregularitiesList.Irregularities_Type3,IrregularitiesList.InstitutionID, IrregularitiesList.PersonnelID
,PersonList.PersonnelName,Admin_Dept.Dept_Name
from IrregularitiesList inner join PersonList
on PersonList.PersonnelID=IrregularitiesList.PersonnelID inner join Admin_Dept
on IrregularitiesList.InstitutionID=Admin_Dept.Dept_Id)a group by LEFT(InstitutionID,9)
