views:

400

answers:

2

I know this is a silly question, but Im a newb

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... sounds simple eh?

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 )

A: 
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.

Jim Dagg
The DISTINCT in the COUNT(DISTINCT isn't necessary because `UNION` removes duplicates
OMG Ponies
THis worked for me. I simply had to add as ColumnA at the end of the union'd select statement
rockit
@OMG: Good call. Thanks for the assist.
Jim Dagg
A: 
SELECT COUNT(*)
FROM
(
SELECT DISTINCT ColumnA From TableX WHERE x = y
UNION
SELECT DISTINCT ColumnA From TableY WHERE y = z
) t

Using a "UNION" will not return duplicates. If you used "UNION ALL" then duplicate ColumnA values from each table WOULD be return.

AdaTheDev
DISTINCT isn't necessary because of the UNION, which removes duplicates. `UNION ALL` does **not** remove duplicates
OMG Ponies
True - I just thought including the distinct in there might be more performant (execution plan is different) - would require a bit more testing to prove/disprove that. Re: UNION ALL, that is what I said :)
AdaTheDev