Q

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.

This was first published in October 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close