Your question, your audience. Choose who sees your identity—and your question—with question security.

Hello all,

I have tables A and B. Each has only one column, column r. A and B may have some duplicate entries. I want to count the total distinct rows in A and B. I've fiddled with various unions, to no avail.

Suggestions will be appreciated.

I have tables A and B. Each has only one column, column r. A and B may have some duplicate entries. I want to count the total distinct rows in A and B. I've fiddled with various unions, to no avail.

Suggestions will be appreciated.

I have two questions:

1) Is this homework?

2) From your last post I assume that you are < mysql 4.1, but can you check by executing the following at the prompt:

select version();

Thanks,

LJ

1. This is not homework. This is a Perl programmer spending his Saturday evening writing SQL queries that feed his Perl script with data to crunch.

2. Version is 4.0.17.

Appreciate your help.

How about first count all the orws in table A. Then all the rows in Table that are not in table A. Then add them. But I'll need the sql for table B.

select count(a.r)-count(b.r) from a left join b on a.r=b.r union select count(*) from b;

is doing.

count(a.r) is the total rows in a,

count(b.r) is the total rows in b that are also in a

count(a.r)-count(b.r) is the total rows in a that are distinct (no corresponding row in b).

union

select count(*) from b is the total rows in b

so you get two rows from that statement: the first row represents the rows in a that don't have corresponding rows in b and the second row is the total number of rows in b. The sum of those two (the summing has to be done in Perl, not in sql because we don't have subqueries) is what you want (I think).

select r from A

while (results) {

$temp_hash{result} = 1

}

select r from B

while (results) {

$temp_hash{result} = 1

}

$count = keys(%temp_hash)

%temp_hash is the union of tables A & B. Then just count the number of elements in the hash. It's not pretty, but it works pretty quickly I think in part thanks to the simplicity of the queries.

Anyway, thanks for the lesson in UNION. That alone was woth it.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Anyway, there's no subquery support in that version, so the easy solution isn't available.

I admit I'm having difficulty myself figuring out how to do this in one sql without subqueries. Assuming the the rows within a particular table are distinct (the tables' rows just aren't distinct when combined), the following will give you two rows whose sum will be what you want:

select count(a.r)-count(b.r) from a left join b on a.r=b.r union select count(*) from b;

Of course, the following will give the all the columns (not the count) which you can then count in perl or use the DBI stuff to get a count of rows:

select r from a union select r from b;

Of course, you'll have to pull all the rows over to perl when you do that and if there a bunch of them, that won't be very efficient. The earlier suggestion would be more efficient.

I'm sorry...it's been too long since I've had to try and work around the no-subquery limitation of mysql. If you upgrade to >=4.1, you can do the obvious thing of:

select count(*) from (select r from a union select r from b) x;

LJ