inner join with group by

Question

there are projects for every branch and every project have item_cost & outlay_fees

all projects

item_cost for every project

outlay_fees for every project

i need to calculate sum ( outlay_fees & item_cost )for every project and i use this :

SELECT
projects.project_no ,
projects.project_address ,
branch.branchName , 
sum(projectwork.item_cost) as totalcost ,
SUM(projectoutlay.projectOutlay_fess) as totalfees 
FROM projects 
INNER JOIN
branch on projects.branch_id = branch.branch_id 
JOIN
projectwork on projectwork.project_id = projects.projects_id 
JOIN
projectoutlay ON projectoutlay.project_id = projects.projects_id
GROUP BY projects.project_no; 

but the result wrong calculation

the results : total Cost & total fees is wrong

Answer 1

 With cte as(
 
 SELECT projectwork.project_id, 
           sum(projectwork.item_cost) as totalcost ,
           SUM(projectoutlay.projectOutlay_fess) as totalfees 
 FROM projectwork INNER JOIN projectoutlay
 ON projectwork.project_id = projectoutlay.projects_id 
 group by
     projectwork.project_id  
 )
 
 SELECT
      projects.project_no ,
      projects.project_address ,
      projects.branch_id,
      cte.totalcost,
      cte.totalfees
From project left JOIN cte
ON projects.project_no = cte.project_id