self join with inner join on other tables together

[RC_820]

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

[pritaeas]

Something like this perhaps? (untested)

SELECT A.username, A.count_ref, B.sum_bonus FROM
(SELECT referral AS username, COUNT(1) AS count_ref FROM members WHERE referral IS NOT NULL GROUP BY referral) AS A
LEFT JOIN (SELECT username, SUM(bonus) AS sum_bonus FROM ref_bonus GROUP BY username) AS B ON A.username = B.username