Example Table

Table1

PlanA_income

PlanB_income

I want the result to be like the below table

What I have tried:

1. I tried JOIN but It is not working while trying to join the complex queries.

Description

1. I want to join the below three tables and get the sum of the income column.
2. I need the sum of planA table and planB table incomes separately and sum of the sums of both tables.

CodePudding user response：

SELECT 
    t1.ID,
    t1.Name,
    SUM(a.Income),
    SUM(b.Income),
    SUM(a.Income)   SUM(b.Income)
FROM 
    Table1 AS t1
    LEFT JOIN PlanA_income AS a ON t1.ID = a.ID
    LEFT JOIN PlanB_income AS b ON t1.ID = b.ID

CodePudding user response：

I created your tables and ran the following query to make sure it is correct

go ahead

SELECT r1.id , r1.name, r1.sum_plan_a, r2.sum_plan_b, sum(r1.sum_plan_a   r2.sum_plan_b) 
from 
(
SELECT T.id, T.name, SUM(PA.income) as sum_plan_a 
FROM Table1 as T JOIN PlanA as PA 
on T.id = PA.id 
GROUP BY T.id 
)as r1 
Join 
(
SELECT T.id, SUM(PB.income) as sum_plan_b 
FROM Table1 as T JOIN PlanB as PB 
on T.id = PB.id 
GROUP BY T.id
)as r2 

on r1.id = r2.id 
GROUP by r1.id;

CodePudding user response：

Aggregate in PlanA_income and PlanB_income and do LEFT joins of Table1 to the results of the aggregation:

SELECT t1.*,
       COALESCE(pa.sum_of_planA_Income, 0) sum_of_planA_Income,
       COALESCE(pb.sum_of_planB_Income, 0) sum_of_planB_Income,
       COALESCE(pa.sum_of_planA_Income, 0)   COALESCE(pb.sum_of_planB_Income, 0) total_income
FROM Table1 t1
LEFT JOIN (SELECT ID, SUM(Income) sum_of_planA_Income FROM PlanA_income GROUP BY ID) pa
ON pa.ID = t1.ID
LEFT JOIN (SELECT ID, SUM(Income) sum_of_planB_Income FROM PlanB_income GROUP BY ID) pb
ON pb.ID = t1.ID;

See the demo.