I have three tables in SQLight database- the first table (PROJWBS) describes the project works and sub-works as the next :

The second table (TASK) describes the works Tasks :

And the third table (TASKRSRC) describes the Tasks Target cost:

I have created this query to order the first table by work & sub-works arrangement and calculate the Works & Sub-works cost :

SELECT PROJWBS.Wbs_id, PROJWBS.Parent_Wbs_id, PROJWBS.Wbs_name,  
COALESCE(subquery.Total_Cost, 0) AS Total_Cost 
FROM PROJWBS 
LEFT JOIN (  
   SELECT TASK.Wbs_id, SUM(TASKRSRC.Target_Cost) as Total_Cost 
    FROM TASK 
    JOIN TASKRSRC ON TASK.Task_id = TASKRSRC.Task_id  
    GROUP BY TASK.Wbs_id 
) as subquery  
ON PROJWBS.Wbs_id = subquery.Wbs_id  
WHERE PROJ_ID = 1
GROUP BY PROJWBS.Wbs_id, PROJWBS.Parent_Wbs_id, PROJWBS.Wbs_name    
ORDER BY CASE  
WHEN PROJWBS.Wbs_id=PROJWBS.PARENT_WBS_ID THEN PROJWBS.PARENT_WBS_ID  
WHEN PROJWBS.Wbs_id<PROJWBS.PARENT_WBS_ID THEN PROJWBS.WBS_ID   
WHEN PROJWBS.Wbs_id>PROJWBS.PARENT_WBS_ID THEN PROJWBS.PARENT_WBS_ID 
END;

The resulting table displays only 0 values ​​for the cost of the main works and calculates the cost only for the subworks. Note :The main tasks have nothing to do with the task table, while the subtasks has.

The query should return a result like this (taking by consideration the order condition inside my query ):

Solution will be approciated .

CodePudding user response：

Consider a second aggregate subquery to sum total cost at project level:

SELECT p.WBS_ID, p.PARENT_WBS_ID, p.WBS_NAME,  
       COALESCE(main_agg.TOTAL_COST, sub_agg.TOTAL_COST, 0) AS TOTAL_COST 
FROM PROJWBS p
LEFT JOIN (  
    SELECT PROJ_ID, SUM(TARGET_COST) AS TOTAL_COST 
    FROM TASKRSRC  
    GROUP BY PROJ_ID
) AS main_agg
  ON p.PROJ_ID = main_agg.PROJ_ID
  AND p.PARENT_WBS_ID = 0
LEFT JOIN (  
    SELECT TASK.WBS_ID, SUM(TASKRSRC.TARGET_COST) AS TARGET_COST 
    FROM TASK 
    JOIN TASKRSRC ON TASK.TASK_ID = TASKRSRC.TASK_ID 
    GROUP BY TASK.WBS_ID
) AS sub_agg
   ON p.WBS_ID = sub_agg.WBS_ID
WHERE p.PROJ_ID = 1
ORDER BY ... ;