Please read the link in my signature on how to format and post SQL questions. It will help get better and faster answers.

So, without any idea about the data or why so many joins are needed to get 2 columns, I would guess that you are going to need to SUM and a GROUP BY to get what you want.

SELECT HL.[Description] AS 'House', SUM(CAST(MSDN.Data AS int)) AS 'Data'

FROM CurrentPupil

INNER JOIN PupilPersonalDetails AS PPD

ON PPD.PupilID = CurrentPupil.PupilID

INNER JOIN PupilCurrentSchool AS PCS

ON PCS.PupilID = PPD.PupilID

INNER JOIN SchoolLookupDetails AS FL

ON PCS.Form = FL.LookupDetailsID

AND FL.LookupID = 1002

INNER JOIN SchoolLookupDetails AS HL

ON PCS.House = HL.LookupDetailsID

AND HL.LookupID = 1001

INNER JOIN MarksheetDataNumeric AS MSDN

ON MSDN.PupilID = PPD.PupilID

INNER JOIN ColumnsMaster AS CM

ON CM.ColumnID = MSDN.ColumnID

AND CM.ColumnTitle LIKE '%week%'

INNER JOIN ClusterMaster AS CLM

ON CLM.ClusterID = SUBSTRING(PPD.SchoolID, 0, 4) --<<=== 0 based string?

INNER JOIN ColumnReportingPeriods AS CRP

ON CRP.ColumnID = CM.ColumnID

INNER JOIN ReportingPeriods AS RP

ON RP.AcademicYear = CLM.CurrentAcademicYear

AND RP.ReportingPeriodID = CRP.ReportingPeriodID

WHERE ('%wc%' = '%wc%')

GROUP BY HL.[Description]

I would also suggest you get a book or get online and look for SQL Basics and Fundamentals. Summing and grouping are SQL 101 concepts.