SQL Statement for displaying sum column in resulted query-CodePudding

I have three tables - the first table describes the project works and sub-works as the next :

PROJECT_ID	WORK_ID	MAIN_WORK_ID	WORK_NAME
1	10	1	Building-01
1	11	1	Building-01

The second table describes the work activities:

ACTIVITY_ID	PROJECT_ID	WORK_ID	ACTIVITY_NAME
1	1	10	Tiling
2	1	10	Metal Works
3	1	11	Wood Works

And the third table includes the activities cost:

ACTIVITY_ID	PROJECT_ID	ACTIVITY_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:

SELECT 
    a.WORK_ID, a.MAIN_WORK_ID, a.WORK_NAME
FROM 
    PROJECTSWORKS a  
WHERE 
    a.PROJECT_ID = 1
ORDER BY 
    CASE 
        WHEN a.WORK_ID = a.MAIN_WORK_ID THEN a.MAIN_WORK_ID
        WHEN a.WORK_ID < a.MAIN_WORK_ID THEN a.WORK_ID
        WHEN a.WORK_ID > a.MAIN_WORK_ID THEN a.MAIN_WORK_ID 
    END

Now I need the resulting table (From my query) to have addition column that contains the total cost for each sub-work, I know that I should use sub query or JOIN statements, but I don't know how to do it inside my query.

The query should return a result like this:

WORK_ID	Total_Cost
10	1600
11	150

CodePudding user response：

You need to join the other two tables to take this result and then to sum(activity_cost) group by the others. Something like this:

    SELECT distinct
    a.WORK_ID, a.MAIN_WORK_ID, a.WORK_NAME,sum(c.activity_cost) total_cost
FROM 
    PROJECTSWORKS a  join activities b on a.project_id=b.project_id 
  and a.work_id=b.work_id
  join activities_cost c on c.activity_id=b.activity_id
WHERE 
    a.PROJEcT_ID = 1
  group by a.WORK_ID, a.MAIN_WORK_ID, a.WORK_NAME

here is an example example