mysql - SQL group by 2 fields -

i got table :

id, user_a, user_b, score 1,       3,      7,   19 2,       8,      3,   20 3,       3,      2,   10 4,       7,      6,   2 5,       3,      6,   7 

i achieve counting number of match per user :

select user, count(*) (     select user_a user tablename     union     select user_b tablename ) dt group user 

i want add user email usertable:

count(*), user,  email 4,       3,      user3@mail.com 2,       7,      user7@mail.com 1,       8,      user8@mail.com 1,       2,      user2@mail.com 2,       6,      user6@mail.com 

i 've tried :

select user, count(*)  (  select user_a user, ut.email    tablename    union        select user_b       tablename ) dt  user not null left join usertable ut on ut.id = user group user 

but there syntax error:

you have error in sql syntax; check manual corresponds mysql server version right syntax use near 'left join usertable ut on ut.id = user group user' 

any idea ?

do union all in derived table, 1 column of users. group by it's result.

select user, count(*) (     select user_a user tablename     union     select user_b tablename ) dt group user 

answer updated question:

select dt.user, ut.email, count(*)  (      select user_a user tablename     union      select user_b tablename ) dt  left join usertable ut on ut.id = dt.user group dt.user, ut.email