0

I have a scenario where I got multiple columns with similar content. I want to count how many distinct values are there in all the columns. Slightly different to the below linked case where content of two columns are looked at as a single attribute/element.

Counting DISTINCT over multiple columns

StudentClubs

My table is as above. I need to go thru all club columns and count how many distinct clubs there are.

The below code I managed only counts distinct rows. Not individual distinct elements in each column.

select count(*) from( select distinct Club1 Club2 from StudentClubs) as ClubCount

The above returns 6

I need it to output 12 as there are 12 clubs in total.

Thanks in advance.

contiago54
  • 27
  • 2

1 Answers1

0

Actually I found the below solution as a interim. First stacking the columns and then using a count distinct. It seem to work at least in this example.

select count(distinct A.Club1)
from (

select Club1 from StudentClubs as A
union all
select Club2 from StudentClubs as A
union all
select Club3 from StudentClubs as A
union all 
select Club4 from StudentClubs as A
) as A

The above outputs 12. Please do share if you have better ways to handle this. Thanks

contiago54
  • 27
  • 2