How can I get count of unique values across two columns in a table?

0
votes

I have a table with column1 and column2 among other columns?

I need a count of unique values across all rows taken from column1 and column2. Possible query: select count(*) from ( (select distinct column1 from table1) UNION (select distinct column2 from table1) );

I also need a count of unique values across all rows as per below :- "column1" + "-" + "column2" "column2" + "-" + "column1"

Possible query: select count(*) from ( (select distinct column1, column2 from table1) UNION (select distinct column2, column1 from table1) )

I think there is a possible flaw with both queries.

Sample Data :

Column 1 Column 2 Value1 null null Value1 Value1 Value2 Value2 Value1 Value4 null Value5 null

Result for Query 1 : 4 (Value1, Value2, Value4, Value5) Result for Query 2 : 5 (Value1, Value1-Value2, Value2-Value1, Value4, Value5)

Null is to be ignored with hyphen being excluded. Or hyphen can be ignored. Not particular about the hyphen.

1
sqloracledistinctmultiple-columns
Sample data and desired results would really help. – Gordon Linoff
How could you possibly get a "unique list of values" by running a "select count(*)? Even if you don't know how to solve your problem, you can't seriously expect a COUNT to give you a LIST OF VALUES. – mathguy
Duplicate ref here : stackoverflow.com/questions/11302617/… – Senthil
I need a count, not a list of values. I corrected the verbiage. – Parag Ahire
I added sample data. – Parag Ahire

1 Answers

0
votes

Sounds like you need something similar to this

SELECT 
COUNT(DISTINCT [a])

FROM    
(SELECT Column1 [a]
FROM TableA
UNION ALL
SELECT Column2
FROM TableA 

) [x]