MySQL Count unique values ​​across two columns and combine these counters for each column.

Question

I have a table that is configured as follows:

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

I basically want to count unique values ​​in both col1 and col2 and display them together with the corresponding unique value. The problem is that col1 does not necessarily contain all the same names as col2, and vice versa. I want to configure it as follows:

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

I can independently select these values ​​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 it’s hard for me to understand how I join these two calculations, especially because the meaning of “Walt” here does not appear in col2.

Answer 1

I assume that you may have more cases than you show in the data. You can have NULL in col1, you can have names that are found 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 resulting table a is the union of all the names that appear in any column. Then create two more views: one with each name from col1 and the number of times how many times this happens, and then another similar derived tale for names in col2.

Answer 2

Try the following:

 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, ac col1, bc 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; 

in fact it must be a full outer join to include names that exist only in col2 - since MySQL does not support full outer join, you must first merge 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 just combine your two original requests into one.

The first takes into account the unique values ​​in col1, so I set 0 as the counter for col2. The second takes into account the unique values ​​in col2, so I set 0 as the counter of col1. A join query combines these two queries together, so now we just need to group and summarize the results. Since there are no connections, I think this solution should be quick.