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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top