Sql: After Joining Tables Sum() Function Returns Wrong Value

I am currently working on a database project and have some issues with joining tables. The initial situation is: four tables: tasks t ~ 50000 records projects p ~ 1000 records w

Solution 1:

At issue is a Cartesian product (where rows in one table are being multiplied by the rows in the other tables). The assumption the following approach is making is that every project has a workload with employees assigned (all of which account for all employees since your query doesn't show the join to the employee table) and tasks. If this isn't the case, then consider doing outer joins versus the inner join.

The idea is to perform each aggregation in its own derived table based on project number. We can then join each derived table by project number to obtain meaningful results.

SELECT
p.NAME,
w.workload_sum AS"Total Workload",
e.employee_count AS"Total Employees",
t.task_count AS"Finished Tasks"from p 
JOIN (select pno, sum(workload) as workload_sum
        from w
       groupby pno) w ON (w.pno=p.pnumber)
JOIN (select pno, count(distinct w.essn) as employee_count
        from w
       groupby pno) e ON (e.pno=p.pnumber)
JOIN (select pno, count(distinct t.name) as task_count
        from t
       groupby pno) t ON (t.pno=p.pnumber)
WHERE t.END_DATE isNOT NULL;