Counting rows in a UNION query
How can I get the total count of the number of records returned, not duplicate, from the union of two queries which return the same set of data but using different where clause?
How can I get the total count of the number of records returned, not duplicate, from the union of two queries which...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
return the same set of data but using different where clause?
For example,
select a, b, c from A, B, C
where {some condition}
UNION
select a, b, c from A, B, C, D
where {some different condition}
Ordinarily, if we are returning detailed rows from the database, and wish to know how many rows there are in the result set, there's usually a function of the programming language that will give it to us (e.g., mysql_num_rows() using PHP with MySQL, QueryName.RecordCount in ColdFusion, etc.).
I'm not sure I understand the "not duplicate" part of your question. If you mean how many rows would be returned by each of the subselects in the union separately, before duplicates are removed by the UNION operator, then we will need to use a separate query with UNION ALL instead of UNION.
select '1st query' as source
, count(*) as rows_returned
from A, B, C
where {some condition}
UNION ALL
select '2nd query'
, count(*)
from A, B, C, D
where {some different condition}
If you mean how many duplicate rows were actually removed, i.e., how many rows actually satisfied both subselects, that's easy too. Just run your original UNION query, use the RecordCount property (or equivalent), and subtract this from the sum of the two counts produced by the above count query.
