CODEWITHSUNDEEP
mysqlsql
lucifer
lucifer

Reputation: 2327

How to count the occurrences of a string value row wise in a table

I have a situation where i have a table containing some values where i have three columns which contains three names in every row ,now what my requirement is that, i have to count that occurrences of names in that table & repetition of occurrences will be treated as 1.And i have to show the occurrences in a column in comma(,) separated format like if occurrences is two then have to show like 1,1.This is my table structure and also i am posting what format i want ,

This is the table i have, 

id  name1  name2 name3 
 1   A       A     B
 2   B       C     D
 3   A       A     A

Now what my requirement is that

 id name1 name2 name3  count 
 1   A       A     B   1,1
 2   B       C     D   1,1,1
 3   A       A     A   1

How to achieve this ,somebody please help

Upvotes: 0

Views: 159

Answers (1)

Bohemian
Bohemian

Reputation: 424973

Use a case:

select id, name1, name2, name3,
  case
    when name1 = name2 and name2 = name3 then '1'
    when name1 != name2 and name1 != name3 and name2 != name3 then '1,1,1'
    else '1,1' end count
from mytable

Note how the testing of cases when all same and all different first mean the else covers all other combinations that can result in 1,1.

If you just want the number of different values (as a numeric value - as per your comments):

select id, name1, name2, name3,
  case
    when name1 = name2 and name2 = name3 then 1
    when name1 != name2 and name1 != name3 and name2 != name3 then 3
    else 2 end count
from mytable

Upvotes: 2

Related Questions