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. — user5683823
– user5683823, Commented Nov 29, 2018 at 1:13
Duplicate ref here : stackoverflow.com/questions/11302617/… — Senthil
– Senthil, Commented Nov 29, 2018 at 1:22
I need a count, not a list of values. I corrected the verbiage. — Parag Ahire
– Parag Ahire, Commented Nov 29, 2018 at 1:23

Simon · Accepted Answer · 2018-11-29 01:33:24Z

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, 2018 at 1:33

Simon

5455 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5683823 Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related