Menu
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
)
541
To get the identical rows (based on two columns agent_code and ord_amount) once from the orders table, the following SQL statement can be used : SQL Code: SELECT DISTINCT agent_code,ord_amount FROM orders WHERE agent_code='A002';
UNION Syntax Remember, UNION combines the result set of two or more SELECT statements, showing only distinct values. The SQL syntax below shows a UNION occurring between two different tables; the columns in both SELECT statements are of the same or matching data types.
A UNION statement effectively does a SELECT DISTINCT on the results set.
The SQL UNION ALL operator does not remove duplicates. If you wish to remove duplicates, try using the UNION operator.
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.
182
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
