[SOLVED] count duplicate column values in one row

count duplicate column values in one row

i have a denormalized table, where i have to count the number of same values in other columns. I'm using the InfiniDB Mysql Storage Engine.

This is my Table:

col1 | col2 | col3
------------------
A    | B    | B
A    | B    | C
A    | A    | A

This is what i expect:

col1Values | col2Values | col3Values
------------------------------------
    1      |     2      |      2     -- Because B is in Col2 and Col3
    1      |     1      |      1     
    3      |     3      |      3

Is there something like

-- function count_values(needle, haystack1, ...haystackN)
select count_values(col1, col1, col2, col3) as col1values -- col1 is needle
    , count_values(col2, col1, col2, col3) as col2values -- col2 is needle
    , count_values(col3, col1, col2, col3) as col3values -- col3 is needle
from table

or am i missing something simple that will do the trick? :-)

Thanks in advance

Roman

Solution

I have found a different, very very simple solution :-)

select if(col1=col1,1,0) + if(col2=col1,1,0) + if(col3=col1,1,0) as col1values -- col1 is needle
from table