Flo Flo - 3 months ago 10
MySQL Question

SQL group by 2 fields

I got that 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 the number of match per user with :

select user, count(*)
from
(
select user_a as user from tablename
union all
select user_b from tablename
) dt
group by user


I now want to add user email from 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 this :

SELECT user, count(*)
FROM (
SELECT user_a AS user, ut.email
FROM tablename
UNION ALL
SELECT user_b
FROM tablename
) dt
WHERE user IS NOT NULL
LEFT JOIN usertable ut ON ut.id = user
GROUP BY user


but there is a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN usertable ut ON ut.id = user
GROUP BY user'


Any idea ?

Answer

Do a UNION ALL in a derived table, to get one column of users. GROUP BY it's result.

select user, count(*)
from
(
    select user_a as user from tablename
    union all
    select user_b from tablename
) dt
group by user

Answer to updated question:

SELECT dt.user, ut.email, count(*) 
FROM
( 
    SELECT user_a AS user FROM tablename
    UNION ALL 
    SELECT user_b FROM tablename
) dt 
LEFT JOIN usertable ut ON ut.id = dt.user
GROUP BY dt.user, ut.email