puls8 puls8 - 3 months ago 21
MySQL Question

Count unique fields mysql

I have a table like this in mysql:

dancer1 | dancer2
------------------
jeff | Julia
john | Megan
jeff | Vanessa


I should be easy, but how do I count the number of unique dancers (in this case 5) The problem looks like questionned here: MySQL Counting Distinct Values on Multiple Columns, but not quite

Answer

A simple union will give you a set of all dancers:

select DISTINCT dancer1 as dancer from table
union 
select DISTINCT dancer2 as dancer from table

And if you want the count:

select count(all_dancers.dancer) from (    
     select DISTINCT dancer1 as dancer from table
     union 
     select DISTINCT dancer2 as dancer from table
) all_dancers
Comments