Aggregate Query Help

I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high.

How do I write a query to obtain the correct totals?


Table 1 - dbo.QM_Results
Columns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumber

Table 2 - dbo.PM_ForecastView
Columns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.Forecast




Select substring(dbo.QM_Results.Special8,0,6) AS Hierarchy,
substring(dbo.QM_Results.SessionName,0,11) As CourseCode,
count(dbo.QM_Results.PersonNumber) TotalAssociates,
sum(dbo.PM_ForecastView.Forecast) TotalForecast

From dbo.QM_Results INNER JOIN dbo.PM_ForecastView
ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)

where SessionMid in ('96882139', '23620891', '45077427', '29721437')
AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')
Group By substring(dbo.QM_Results.Special8,0,6),
substring(dbo.QM_Results.SessionName,0,11)




Sample of data returned with my current query.

Hierarchy CourseCode TotalAssociates TotalForecast
EZHBA CARD167200 1179 141480
EZHBA CARD167201 1416 169920
EZHBA CARD167202 1119 134280
EZHBA CARD167204 99 11880

Results when I run aggregate query separately
Actual Total taken
Hierarchy CourseCode TotalTaken
EZHBA CARD167200 393
EZHBA CARD167201 472
EZHBA CARD167202 373
EZHBA CARD167204 33


Forecasted Total taken
Hierarchy CourseCode Forecast
EZHBA CARD167200 999
EZHBA CARD167201 900
EZHBA CARD167202 800
EZHBA CARD167204 800