Count distinct rows returned from UNION
-
This question was posed to me by a co-worker and I found that I couldn't answer it as easily as I first thought. What we would like to do is to return the number of DISTINCT values returned by a statement including a union. It's easy enough to do with a temporary table or a number of other means, but I couldn't figure out how to do it in a single select statement.
ex. (what we'd like to do)
select count(distinct
(select fund from tableA Where ....)
Union
(select fund from tableB where....))
-- the same fund may be in both tables --
Of course, this doesn't work but is there a way?
Thanks in advance.
-bob
-
how about :
select count(distinct fund)
FROM
((select fund from tableA Where ....)
Union
(select fund from tableB where....)) Q
* Noel -
That works great. Thanks! I don't remember ever seeing the 'Q' at the end of the statement like that.
-
The Q is just to Alias the Subquery
* Noel -
Also, something to remember about Unions:
If you use "Union", this will remove any duplication,
whereas if you use "Union All", this will retain duplicate rows.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply