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 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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments