This is done by a series of sub-datasets Inner Joined together, one after the other.

Counts per area are joined in one at a time.

The join done using a single ID that is present throughout all the tables.

Select

AreaTable.Area

,CountTable1.Count1 As CoursesCount

,CountTable2.Count2 As AttendeesCount

,CountTable3.Count3 As TeachersCount

,CountTable4.Count4 As PassCount

,CountTable4.Count5 As FailCount

From

AreaTable

Left Outer Join — For first lot of counts per area.

(

Select

AreaID

,Count(CourseID) As Count1

From

CourseTable

Group By

AreaID

) As CountTable1 On AreaTable.AreaID = CountTable1.AreaID

Left Outer Join — For second lot of counts per area.

(

Select

AreaID

,Count(AttendeeID) As Count2

From

AttendeeTable

Group By

AreaID

) As CountTable2 On AreaTable.AreaID = CountTable2.AreaID

Left Outer Join — For third lot of counts per area.

(

Select

AreaID

,Count(TeacherID) As Count3

From

TeacherTable

Group By

AreaID

) As CountTable3 On AreaTable.AreaID = CountTable3.AreaID

Left Outer Join — For fourth lot of counts per area.

(

Select

AreaID

,Count(Pass) As Count4

,Count(Fail) As Count5

From

TestResultTable

Group By

AreaID

) As CountTable4 On AreaTable.AreaID = CountTable4.AreaID