Count occurrences of distinct values in 2 fields

Question

I am trying to find a MySQL query that will find distinct values in a particular field, count the number of occurrences of that value in 2 fields (1_user, 2_user) and then order the results by the count.

example db

+------+-----------+-----------+
|   id | 1_user    | 2_user    |
+------+-----------+-----------+
|    1 |       2   | 1         | 
|    2 |       3   | 2         | 
|    3 |       8   | 7         | 
|    4 |       1   | 8         | 
|    5 |       2   | 8         |
|    6 |       3   | 8         |  
+------+-----------+-----------+

expected result

user       count
-----      -----
8          4
2          3
3          2
1          2

Answer 1

The Query

SELECT user, count(*) AS count
FROM
(
    SELECT 1_user AS USER FROM test

    UNION ALL

    SELECT 2_user FROM test
) AS all_users
GROUP BY user
ORDER BY count DESC

Explanation

List all the users in the first column.

SELECT 1_user AS USER FROM test

Combine them with the users from the second column.

UNION ALL
SELECT 2_user FROM test

The trick here is the UNION ALL which preserves duplicate values.

The rest is easy -- select the results you want from the subquery:

SELECT user, count(*) AS count

aggregate by user:

GROUP BY user

and prescribe the order:

ORDER BY count DESC

Answer 2

SELECT u, count(u) AS cnt 
FROM (
    SELECT 1_user AS u FROM table
    UNION ALL
    SELECT 2_user AS u FROM table
) subquery 
GROUP BY u
ORDER by cnt DESC