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

Question

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.

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. — , Nov 29 '18 at 01:13
Duplicate ref here : https://stackoverflow.com/questions/11302617/select-distinct-values-from-multiple-columns-in-same-table — Senthil, Nov 29 '18 at 01:22
I need a count, not a list of values. I corrected the verbiage. — Parag Ahire, Nov 29 '18 at 01:23

score 0 · Answer 1 · answered Nov 29 '18 at 01:33

0

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]

answered Nov 29 '18 at 01:33

Simon

496
4
19

1

Since the result must be de-duplicated in the end anyway, why not simply use `UNION` instead of `UNION ALL`? Then you won't need to count DISTINCT. – Nov 29 '18 at 02:19

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

1 Answers1