it would work but I think the "semantics" of

select sum(cnt)
 from ( select cnt from t1
        union all
        select cnt from t2 )

are more appealing to me...


there are so many ways we could have done this

your scalar subqueries would work.

as would

select count(*)
  from (select null from table_a where value_x is not null
        union all
        select distinct value_x from table_b where value_x is not null);

I typically post "an answer", not all possible ones (as that could be an infinite set :) )

I disagree with your analysis.


If you put

a,b,c,a,z,x into a single table.....

and did a count(*) on it

what would you get?


You would get 6

why? because the number of rows is six.


If you want the count(DISTINCT x) - then by all means, use that - but don't tell me that six is wrong, it isn't.

You might have *a different question* than above, but the answer to "how many rows in two tables each having three rows" is six....

like I said, you have *a different question* than others.


the original poster wanted

a) count of non-null values of value_x from table_a
b) count of distinct non-null values of value_x from table_b

added together. That is:

select sum(cnt)
  from (select count(value_x) cnt from table_a
        UNION ALL
        select count(distinct value_x) frmo table_b
       )

Later, I answered that same question differently:

select count(*)
  from (select null from table_a where value_x is not null
        union all
        select distinct value_x from table_b where value_x is not null);

there union would get the WRONG answer given the question asked...



You quite simply had a different question - so the answers provided previously are not *wrong*, they just are not the answers to YOUR question.