MYSQL Count unique values for each row

Question

I have mysql database structure and values:

name  | x | y | z
------------------
name1 | 1 | 1 | 2
name2 | 2 | 1 | 3 
name3 | 1 | 1 | 1

And now i need add extra column with counter unique values (x,y,z) for each row:

name  | x | y | z | unique
--------------------------
name1 | 1 | 1 | 2 | 2
name2 | 2 | 1 | 3 | 3
name3 | 1 | 1 | 1 | 1

How can i do that?

Community · Accepted Answer · 2017-05-23 12:16:06Z

1

you can use select count distinct

SELECT *,COUNT (DISTINCT column-name)  FROM table-name;

more answers here > Counting DISTINCT over multiple columns, its already been answered.

CommunityBot

11 silver badge

answered May 7, 2016 at 17:03

Freak

1151 gold badge1 silver badge16 bronze badges

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

1 Comment

did this answer help?

Matt · Accepted Answer · 2016-05-07 17:16:15Z

0

If you don't need it to scale massively you can do this

SELECT *, 
(CASE 
 WHEN x=y=z THEN 1
 WHEN x=y OR x=z OR y=z THEN 2
 ELSE 3
END) as `unique`
FROM `table`

answered May 7, 2016 at 17:16

Matt

1,7573 gold badges13 silver badges26 bronze badges

Unfortunately i have more columns and several millions of rows. Any other idea?

@kuguary are you using this in conjunction with php or just direct sql queries?