MySQL Counting unique values over two columns and joining those counts for each column

Question

I have a table that is setup like this:

  +----+-------+-------+
  | id | col1  | col2  |
  +----+-------+-------+
  |  1 | John  | Mike  |
  |  2 | Mike  | John  |
  |  3 | Marty | John  |
  |  4 | Walt  | Marty |
  |  5 | Walt  | Mike  |
  +----+-------+-------+

I basically want to count the unique values in both col1 and col2 and display them alongside the appropriate unique value. The problem is that col1 doesn't necessarily contain all the same names that col2 has, and vice versa. I'm looking to get it setup like this:

 +-------+-------+------+
 | names | col1  | col1 |
 +-------+-------+------+
 | John  |     1 |    2 |
 | Marty |     1 |    1 |
 | Mike  |     1 |    2 |
 | Walt  |     2 | NULL |
 +-------+-------+------+

I can select these values independently using:

  SELECT col1, count(col1) as count FROM example GROUP BY col1; 

OR

  SELECT col2, count(col2) as count FROM example GROUP BY col2;

But I'm having one hard time understand how I join those two counts together, especially because the value here "Walt" doesn't appear in col2.

Answer 1

I'm assuming you could have more cases than you show in the data. You could have NULLs in col1, you could have names that occur only in col1 or only in col2, etc.

SELECT a.name, c1.`count`, c2.`count`
FROM (SELECT col1 AS name FROM `Table` UNION SELECT col2 FROM `Table`) a
LEFT JOIN (SELECT col1, COUNT(*) AS `count` FROM `Table` GROUP BY col1) c1 
  ON a.name = c1.col1
LEFT JOIN (SELECT col2, COUNT(*) AS `count` FROM `Table` GROUP BY col2) c2 
  ON a.name = c2.col2;

Explanation:
The derived table a is a union of all names that appear in either column. Then make two more derived tables, one with each each name from col1 and the count of how many times it occurs, and then another similar derived tale for names in col2.

Answer 2

Try this:

SELECT 
  t1.col1, 
  count(t2.col2), 
  COUNT(t1.col2) 
FROM table1 t1
LEFT JOIN 
(
   SELECT col2 
   FROM Table1
) t2 ON t1.col1 = t2.col2
GROUP BY t1.col1;

SQL Fiddle Demo

Answer 3

select coalesce(a.col1,b.col2) names, a.c col1, b.c col2 from
(select col1, count(*) c from table1 group by col1) a
left outer join
(select col2, count(*) c from table1 group by col2) b
on a.col1 = b.col2;

actually it would need to be a full outer join to include names that only exist in col2 - since MySQL doesn't support full outer joins you have to first union them together as in Bill's answer.

Answer 4

This could also be a solution:

select names, sum(totalc1), sum(totalc2)
from
  (select col1 as names, count(col1) as totalc1, 0 as totalc2
   from your_table group by col1
   union
   select col2 as names, 0 as totalc1, count(col2) as totalc2
   from your_table group by col2) t
group by names

I'm just combining your two original queries into one.

The first one counts the unique values in col1, so i'm setting 0 as the count of col2. The second one counts the unique values in col2, so i'm setting 0 as the count of col1. A union query combines these two queries together, so we now just have to group by and sum the results. Since there are no joins involved, i think this solution should be fast.