CODEWITHSUNDEEP
mysqlsqljoingroup-by
Bruce
Bruce

Reputation: 1071

Get sum of two joined tables where unique data may be in both tables

I have two tables that I need a combined sum after join where data may be present in one table but not the other.

Tables

| username | commission | status      | username | commission | status
__________________________________    __________________________________
| userA    | 3          | pending     | userA    | 1          | pending
| userA    | 2          | pending     | userA    | 1          | pending
| userB    | 3          | pending     | userC    | 3          | pending
| userB    | 2          | pending     | userD    | 2          | pending
| userC    | 1          | pending     | userD    | 2          | pending
| userC    | 1          | pending     | userD    | 2          | pending

Above are examples of two tables. I am trying to get the following results.

| username | commission | status   
_________________________________
| userA    | 7          | pending
| userB    | 5          | pending
| userC    | 5          | pending
| userD    | 6          | pending

The problem is userB has no matching column in Table 2 and userD has not matching column in Table1 so I can't do a standard INNER JOIN on username as a LEFT will exclude userD and a RIGHT will exclude userB.

What type of JOIN should I be using and perhaps an example?

Upvotes: 0

Views: 16

Answers (1)

GMB
GMB

Reputation: 222462

In MySQL, you can use union all and aggregation:

select username, sum(commission) commission, status
from (
    select username, comission, status from table1 t1
    union all 
    select username, comission, status from table2 t2
) t
group by username, status

Upvotes: 1

Related Questions