Issue with summing values form joined tables

Question

I have a main table named Jobs. Each job can have multiple levels. Each level can have multiple sub-levels, each sub-level can have multiple tasks. All tasks contain an EstimatedHours column.

Jobs Table:

JobId	JobName
1	Job1
2	Job2

Levels Table:

LevelId	JobId	LevelName
1	1	Job 1 Level 1
2	1	Job 1 Level 2
3	2	Job 2 Level 1

SubLevels Table:

SubLevelId	LevelId	SubLevelName
1	1	Job 1 Level 1 Sub 1
2	1	Job 1 Level 1 Sub 2
3	2	Job 1 Level 2 Sub 1
4	2	Job 1 Level 2 Sub 2
5	3	Job 2 Level 1 Sub 1

DoorTasks Table:

TaskId	SubLevelId	TaskName	EstimatedHours
1	1	Job 1 Door Task 1	5
2	1	Job 1 Door Task 2	5
3	1	Job 1 Door Task 3	5
4	2	Job 1 Door Task 4	5
5	5	Job 2 Door Task 5	1

DoorFrameTasks Table:

TaskId	SubLevelId	TaskName	EstimatedHours
1	1	Job 1 Door Frame Task 1	5
2	1	Job 1 Door Frame Task 2	5
3	1	Job 1 Door Frame Task 3	5
4	2	Job 1 Door Frame Task 4	5
5	5	Job 2 Door Frame Task 5	2

DoorHardwareTasks Table:

TaskId	SubLevelId	TaskName	EstimatedHours
1	1	Job 1 Door Frame Task 1	5
2	1	Job 1 Door Frame Task 2	5
3	1	Job 1 Door Frame Task 3	5
4	5	Job 2 Door Frame Task 4	3
5	5	Job 2 Door Frame Task 5	4

My current query is

SELECT 
    j.JobName,
    SUM(dt.EstimatedHours) + SUM(df.EstimatedHours) + SUM(dh.EstimatedHours) AS "Total Estimated Hours"
FROM  
    Jobs AS j 
LEFT OUTER JOIN
    Levels AS l ON l.JobId = j.JobId 
LEFT OUTER JOIN 
    SubLevels AS sl ON sl.LevelId = l.LevelId 
LEFT OUTER JOIN
    DoorTasks AS dt ON dt.SubLevelId = sl.SubLevelId 
LEFT OUTER JOIN
    DoorFrameTasks AS df ON df.SubLevelId = sl.SubLevelId 
LEFT OUTER JOIN
    DoorHardwareTasks AS dh ON dh.SubLevelId = sl.SubLevelId         
GROUP BY
    j.JobName

I need a query that will sum all Estimated hours for each job and display the JobName and Total EstimatedHours. You can see an example of the table structures and my current query HERE. My problem is, some of the tasks' estimated hours are being added multiple times.

Answer 1

with t as 
          (
           select sublevelid, estimatedhours
           from   DoorHardwareTasks
           union all
           select sublevelid, estimatedhours
           from   DoorFrameTasks
           union all 
           select sublevelid, estimatedhours
           from   DoorTasks
          )

select   jobid 
        ,jobname
        ,sum(estimatedhours)
from   t join SubLevels using(sublevelid) 
         join Levels    using(levelid) 
         join Jobs      using(jobid)
group by jobid, jobname

jobid	jobname	sum
1	Job 1	55
2	Job 2	10

Fiddle