hii how can i make self join and sum data in other table by username
i have table like this
members (id, username, referral)
ref_bonus (id, username, bonus)
members
id | username | referral
----------------------
1 id1 id2
2 id2 null
3 id3 id2
ref_bonus
id | username | bonus
----------------------
11 id2 1.00
21 id2 1.00
31 id2 1.00
the result i want is select all member have referral and sum bonus in ref_bonus
example result is like this :
id | username | count_ref | sum_bonus
----------------------
1 d2 2 | 3.0