Getting a distinct value across 2 union sql server tables

sql union distinct distinct-values
rockit picture rockit · Dec 11, 2009 · Viewed 64.5k times · Source

I'm trying to get all distinct values across 2 tables using a union.

The idea is to get a count of all unique values in the columnA column without repeats so that I can get a summation of all columns that contain a unique columnA.

This is what I tried (sql server express 2008)

select 
    count(Distinct ColumnA) 
from 
( 
    select Distinct ColumnA as ColumnA from tableX where x = y
    union
    select Distinct ColumnA as ColumnA from tableY where y=z
)

Answer

Jim Dagg picture Jim Dagg · Dec 11, 2009 
SELECT COUNT(distinct tmp.ColumnA) FROM ( (SELECT ColumnA FROM TableX WHERE x=y) 
UNION (SELECT ColumnA FROM TableY WHERE y=z) ) as tmp

The extra distincts on TableX and TableY aren't necessary; they'll get stripped in the tmp.ColumnA clause. Declaring a temporary table should eliminate the ambiguity that might've prevented your query from executing.

Related questions

How to execute UNION without sorting? (SQL)

UNION joins two results and remove duplicates, while UNION ALL does not remove duplicates. UNION also sort the final output. What I want is the UNION ALL without duplicates and without the sort. Is that possible? The reason for this …

Read More
Select distinct values from multiple columns in same table

I am trying to construct a single SQL statement that returns unique, non-null values from multiple columns all located in the same table. SELECT distinct tbl_data.code_1 FROM tbl_data WHERE tbl_data.code_1 is not null UNION SELECT …

Read More
Adding DISTINCT to a UNION query

How do I get distinct title.id's from this: SELECT Title.id, Title.title FROM titles as Title HAVING points > 0 UNION ALL SELECT Title.id, Title.title FROM titles as Title HAVING points > 1 There is more to the …

Read More