Count unique fields mysql

Question

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

you need the count as 5 as given here..?

safin chacko
– safin chacko

2016-08-31 12:54:49 +00:00
Commented Aug 31, 2016 at 12:54 — safin chacko
– safin chacko, Commented Aug 31, 2016 at 12:54

sisanared · Accepted Answer · 2016-08-31 13:11:00Z

2

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

edited Aug 31, 2016 at 13:11

answered Aug 31, 2016 at 12:43

sisanared

4,3072 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sisanared Over a year ago

@safinchacko I don't think a distinct is possible on multiple columns at the same time. I just tried it out in MySQL

puls8 Over a year ago

Only with UNION the count is right, thanks @shravster !

safin chacko · Accepted Answer · 2016-08-31 12:50:03Z

0

try this query

select  count( distinct dancer1,dancer2  ) from table

answered Aug 31, 2016 at 12:50

safin chacko

1,3901 gold badge11 silver badges18 bronze badges

Comments

Strawberry · Accepted Answer · 2016-08-31 13:04:56Z

0

SELECT COUNT(DISTINCT dancer) 
  FROM 
     ( SELECT dancer1 dancer 
         FROM my_table 
        UNION 
       SELECT dancer2 
         FROM my_table
     ) x;

answered Aug 31, 2016 at 13:04

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

Count unique fields mysql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related