compare two table dispaly total distinct count

VINAY KUMAR

3y884C#

we have two tables: table1 and table2.

table1:-

tb1234

tb1236

tb1234

table-2

tb1234

tb1238

i want display only total distinct recrords count only(tablae 1 and table 2) like 3

iam using below query displays like 4

select sum(tbl.AllCount)
From
(
select count(DISTINCT id) as AllCount from Table_1
UNION ALL
select count(DISTINCT id) as AllCount from Table_2
)tbl

plz help me

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Accepted answer

WITH CombinedCTE AS (
    SELECT id FROM Table_1
    UNION
    SELECT id FROM Table_2
)
SELECT COUNT(DISTINCT id) AS TotalCount
FROM CombinedCTE;

SELECT COUNT(DISTINCT id) as TotalDistinctCount FROM ( SELECT id FROM Table_1 UNION SELECT id FROM Table_2 ) AS CombinedTables;

SELECT COUNT(DISTINCT id) as TotalDistinctCount
FROM (
    SELECT id FROM Table_1
    UNION
    SELECT id FROM Table_2
) AS CombinedTables;