CODEWITHSUNDEEP
mysqlsql
biedlonka
biedlonka

Reputation: 21

SQL - distinct number of elements in the row

Let’s say I have three columns: dim1, dim2, dim3 - all of int type values.

I want to calculate the number of distinct values in the row - so in case for row of ID #13 dim1=20, dim2=30 and dim3=20, the value stored in the newly created column would be equal 2.

Is this possible somehow? I tried various combinations on COUNT DISTINCT on multiple columns, didn’t work so far.

Upvotes: 0

Views: 51

Answers (2)

Allan
Allan

Reputation: 17429

Aggregate functions like count work across rows, not columns. I see two ways that you could solve this:

1) You could use case statements to solve this (this solution becomes significantly more complicated with more than 3 fields):

select dim1, dim2, dim3,
       case when dim1 <> dim2 then 1 else 0 end
       + case when dim1 <> dim3 then 1 else 0 end
       + case when dim2 <> dim3 then 1 else 0 end as cnt
from your_table

2) Assuming you have some kind of ID on each row, you could use union to turn your data into more of a key/value set, which would allow you to use count:

select dim1, dim2, dim3, cnt
from your_table
     join (select id, count(distinct dim) as cnt from
                  (select id, dim1 as dim from your_table
                   union all 
                   select id, dim2 from your_table
                   union all 
                   select id, dim3 from your_table)
           group by id) c
     on your_table.id = c.id

Upvotes: 2

Robert Kock
Robert Kock

Reputation: 6018

Maybe something like this (I didn't try however)

SELECT row_id,
       CASE WHEN dim1=dim2 THEN CASE WHEN dim1=dim3 THEN 3 ELSE 2 END
            WHEN dim1=dim3 OR dim2=dim3 THEN 2
            ELSE 1
       END AS nr_equal
FROM   ....

Upvotes: 0

Related Questions