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_Namefrom   IrregularitiesList inner join PersonListon PersonList.PersonnelID=IrregularitiesList.PersonnelID inner join Admin_Depton  IrregularitiesList.InstitutionID=Admin_Dept.Dept_Id)a group by LEFT(InstitutionID,9)