July 10, 2007 - 11pm Central time zone
Reviewer: A reader
Any reason why you would use a union do this over
select (select count(*) from tablea)+(select count(*) from tableb) from dual
Not saying it's better (I can't test it right now so it might not even work!)
Followup July 11, 2007 - 8am Central time zone:
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 :) )
why ?
July 11, 2007 - 4am Central time zone
Reviewer: A reader
Any reason why you ask this ?
si tacuisses philosophus mansisses
Great Help! :-)
July 11, 2007 - 7am Central time zone
Reviewer: Judy Onley from Chantilly, VA
I think this logic is wrong.
February 18, 2009 - 11pm Central time zone
Reviewer: Ron Trask from Denver, CO (USA)
A UNION returns a distinct combination. So if there are members of the second select which match
members of the first select the count is going to be thrown off.
To illustrate, if the first select were to return ( a,b,c ) and the second query returned (a,z,x)
the correct count should be 5 (a,b,c,z,x). The above query would return 6.
The second approach suggested has the same failing.
Followup February 19, 2009 - 7am Central time zone:
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....
The third is correct except for the union all
February 19, 2009 - 12am Central time zone
Reviewer: Ron Trask from Denver, CO (USA)
I looked at the responses again and tried a modified version of the approach you said you liked
best, which yielded the correct answer for me. I think the correct answer to the original question
is:
select count(*) from (
select People from table_a
union
select People from table_b
);
Thanks for all your efforts, I have learned a lot from this site. --- Ron
Followup February 19, 2009 - 7am Central time zone:
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.
|