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}

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: