Join two tables with similar columns

Question

I hva two tables as is:

• Table univers:

client	commercial
Axel	Tony
Mike	Jhon
Chris	Jhon

• Table transactions

frclient	toclient	commission
Axel	NA	10
Mike	NA	8
NA	Mike	15
Axel	NA	4
NA	Chris	5
Chris	NA	5

Now, I would like to be able to have the total commissions generated by Jhon and Tony's clients, a bit like:

commercials	commissions
Jhon	33
Tony	14

The only element that links the univers table and the transactions table are the customer IDs, namely their names, nothing else.

How can I proceed please? THANKS.

Answer 1

SELECT t1.commercial, SUM(t2.commission) commissions
FROM univers t1
JOIN transactions t2 ON t1.client IN (t2.frclient, t2.toclient)
GROUP BY 1