-- creating data structure create table categories ( catId int identity (1,1) primary key, catName varchar(30) ) insert categories values ('cat1') insert categories values ('cat2') create table activities ( actId int identity (1,1) primary key, actName varchar(30), catId int foreign key references categories (catId) ) insert activities values ('act1', 1) insert activities values ('act2', 2) insert activities values ('act3', 2) create table subActivities_states ( subActStId int identity (1,1) primary key, subAtcStName varchar(30) ) insert subActivities_states values ('not started') insert subActivities_states values ('ongoing') insert subActivities_states values ('finished') create table subActivities ( subActId int identity (1,1) primary key, subActName varchar(30), subActStId int foreign key references subActivities_states (subActStId), actId int foreign key references activities (actId) ) insert subActivities values ('act1-subAct1-not started', 1, 1) insert subActivities values ('act1-subAct2-ongoing', 2, 1) insert subActivities values ('act1-subAct3-finished', 3, 1) insert subActivities values ('act2-subAct1-not started', 1, 2) insert subActivities values ('act2-subAct2-ongoing', 2, 2) insert subActivities values ('act3-subAct1-not started', 1, 3) insert subActivities values ('act3-subAct2-finished', 3, 3) go -- selecting total subactivities and total finished subactivities by category select catName, SUM(subCnt) subCntTotal, SUM(subCntFinished) subCntFinishedTotal from ( select catName, COUNT(sub.subActId) subCnt, ( select COUNT(1) from subActivities_states st /* without this join, the query throws an error */ join subActivities sub on sub.actId = act.actId where st.subActStId = sub.subActStId and st.subAtcStName = 'finished' ) subCntFinished from activities act join categories cat on cat.catId = act.catId join subActivities sub on sub.actId = act.actId group by catName, act.actId ) subQuery group by catName go -- dropping tables drop table subActivities drop table subActivities_states drop table activities drop table categories