15,847,427 members
See more:
I want to join the below three tables and get the sum of the income column.

Table1
ID | Name
----------
1 | Sam
2 | Anderson

PlanA_income
ID | Income
----------
1 | 150
2 | 25
1 | 300

PlanB_income
ID | Income
----------
2 | 150
2 | 25
1 | 300

I want the result to be like the below table
```ID | Name     | sum of planA income | sum of planB income | Total Income  |
---------------------------------------------------------------------------
1  | Sam      |  450                |   300               |       750     |
2  | Anderson |   25                |   175               |       200     |```

What I have tried:

I tried JOIN but It is not working while trying to join the complex queries.
Posted
Updated 9-Jul-22 2:39am

## Solution 1

Start simply: join two tables and see what you get:
SQL
```SELECT a,ID, a.Name, b.Income
FROM Table1 a
JOIN PlanA_income b ON a.ID = b.ID```

That will give you
```ID	Name	Income
1	Sam       	150
1	Sam       	300
2	Anderson   	 25```
Now, convert that to totalized rows:
SQL
```SELECT a.ID, a.Name, b.TotalIncome AS [sum of planA income]
FROM Table1 a
JOIN (SELECT ID, SUM(Income) AS TotalIncome FROM PlanA_income GROUP BY ID) b
ON a.ID = b.ID```
Which gives you this:
```ID	Name	sum of planA income
1	Sam       	450
2	Anderson  	 25```

And you are most of the way there.

Now, you should be able to add in the third table yourself, and then add the two totals together to complete the exercise.