SQL SUM function with inner joins adding more than once

Question

I'm trying to sum columns on 2 tables using inner joins but the sums are being performed more than once. The sums work fine when ran independently but not when they're run together. The joins seem to be the problem but I can't work out the correct way to lay out this query.

This is my code -

SELECT [tblMedias].[Media_Name],[tblMedias].[Prod_Rate], 
sum(CAST(tblQuickPlans.[TotalHours] AS decimal(10,2))) as '[TotalHours]', sum(CAST(tblhourly_stats.[ActTotal] AS decimal(10,2))) as '[ActTotal]', 
sum(CAST(tblhourly_stats.[ClrdTotal] AS decimal(10,2))) as '[ClrdTotal]', sum(CAST(tblhourly_stats.[InProcTotal] AS decimal(10,2))) as '[InProcTotal]', sum(CAST(tblhourly_stats.[EscTotal] AS decimal(10,2))) as '[EscTotal]' 

FROM [tblMedias]

INNER JOIN [tblhourly_stats] ON [tblMedias].Media_Name = tblhourly_stats.Media
INNER JOIN [tblQuickPlans] ON [tblMedias].Media_Name = [tblQuickPlans].media

WHERE tblhourly_stats.Media = @passedmedia 
AND tblhourly_stats.[Date] between cast(@startDate As datetime) AND cast(@endDate As datetime) 
AND tblQuickPlans.[Date] between @startDate AND @endDate 
AND tblhourly_stats.[Date] between cast(@startDate As datetime) AND cast(@endDate As datetime) 

GROUP BY [tblMedias].[Media_Name],[tblMedias].[Prod_Rate];

Currently the results below are returned -

Total Hours = 54 
ActTotal = 12 
ClrdTotal = 70 
InProcTotal = 8 
EscTotal = 78
  
But the correct data should be -

Total Hours = 18 
ActTotal = 6 
ClrdTotal = 35 
InProcTotal = 24 
EscTotal = 39

Answer 1

Per media you join every tblhourly_stats row with every tblQuickPlans row. So if you have 3 tblhourly_stats rows and 2 tblQuickPlans rows for a media, you'll get each tblQuickPlans value three-fold and every tblhourly_stats value two-fold.

What you really want to do is join a media's tblQuickPlans sums with its tblhourly_stats sums. So, build the sums first, then join.

SELECT 
  m.[Media_Name],
  m.[Prod_Rate], 
  qp.total_hours,
  hs.act_total, 
  hs.clrd_total, 
  hs.inproc_total,
  hs.esc_total 
FROM [tblMedias] m
INNER JOIN
(
  SELECT
    media,
    SUM(CAST([TotalHours] AS DECIMAL(10,2))) AS total_hours
  FROM [tblQuickPlans]
  WHERE [Date] BETWEEN @startdate AND @enddate 
  GROUP BY media
) qp ON qp.media = m.media_name
INNER JOIN 
(
  SELECT
    media, 
    SUM(CAST([ActTotal] AS DECIMAL(10,2))) AS act_total, 
    SUM(CAST([ClrdTotal] AS DECIMAL(10,2))) AS clrd_total, 
    SUM(CAST([InProcTotal] AS DECIMAL(10,2))) AS inproc_total, 
    SUM(CAST([EscTotal] AS DECIMAL(10,2))) AS esc_total 
  FROM [tblhourly_stats]
  WHERE [Date] BETWEEN CAST(@startdate AS DATETIME) AND CAST(@enddate AS DATETIME) 
  GROUP BY media
) hs ON hs.media = m.media_name
WHERE m.media_name = @passedmedia 
ORDER BY m.[Media_Name];

Instead of inner joins you can use CROSS APPLY, if you like this better.

If you want to include media that has no row in tblQuickPlans and/or tblhourly_stats, use LEFT OUTER JOIN (or OUTER APPLY) instead.