mysql - self join query to get result in one table -

i have table below.

+------+----------+--------+ |  id  |sender_id |receiver_id| +------+----------+--------+ |  1   | 1        | 4      | |  2   | 1        | 34     | |  3   | 4        | 1      | |  4   | 11       | 8      | |  5   | 24       | 4      | |  6   | 11       | 5      | +------+----------+--------+ 

i want to see result bellow.

+------+----------+--------+ |user_id|sent     |receive  | +------+----------+--------+ |  1   | 2        | 1      | |  4   | 1        | 2      | |  5   | 0        | 1      | |  8   | 0        | 1      | | 11   | 2        | 0      | | 24   | 1        | 0      | | 34   | 0        | 1      | +------+----------+--------+ 

i want show result in 1 table, column user_id(all unique sender_id & receiver_id) user_id, sent (count(sender_id) sent, count(receiver_id)) receive. can result in 1 table every user sent & receive message number. trying self join query don't expected result.

i use query:

select user_id, sum(sent), sum(receive) (   select sender_id user_id, count(*) sent, 0 receive   tablename   group sender_id   union        select receiver_id user_id, 0 sent, count(*) receive   tablename   group receiver_id ) s group user_id 

please see fiddle here.