CODEWITHSUNDEEP
sqlsqlite
Issam
Issam

Reputation: 186

Geting sum for main and sub-main categories from three tables in SQLite database

I have three tables in SQLite database; the first table (PROJWBS) describes the project works and sub-works like this:

PROJ_ID WBS_ID PARENT_WWBS_ID WBS_NAME
1 1 0 MAIN WORK
1 10 1 WORK-01
1 11 1 WORK-02

The second table (TASK) describes the work tasks:

TASK_ID PROJ_ID WBS_ID TASK_NAME
1 1 10 Tiling
2 1 10 Metal Works
3 1 11 Wood Works

And the third table (TASKRSRC) describes the tasks target cost:

TASK_ID PROJ_ID TARGET_COST
1 1 500
1 1 750
2 1 350
3 1 150

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 ):

WBS_ID WBS_NAME Total_Cost
1 MAIN WORK 1750
10 WORK-01 1600
11 WORK-02 150

Solution will be appreciated.

Upvotes: 1

Views: 60

Answers (1)

Parfait
Parfait

Reputation: 107767

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 ... ;

Upvotes: 3

Related Questions