The following SQL returns 50 rows:

select cpr2.rxclaimnbr from claimspaidreversed as cpr1 join claimspaidreversed as cpr2 on (cpr1.rxclaimnbr=cpr2.rxclaimnbr) where cpr1.datesbm='2004-06-04' group by cpr2.rxclaimnbr having mod(count(cpr2.claimsts),2)=0

So I threw a count() around cpr2.rxclaimnbr hoping to get back one row containing the number 50.

Instead I get back 50 rows, with what looks like the number of items in each group.

How do I get back the number of rows?

You should be able to use:-PatP

You would think COUNT(DISTINCT column) would work, wouldn't you? Instead I get back 50 rows, all containing the number 1 instead of the number of items in each group as before.
I've also got this posted over in the MySQL forum, I think it's a bug. And I thought queries with COUNT were supposed to be MySQL's strong point :-)

You DID remove the GROUP BY clause, right?

-PatP

Nope, I need it so HAVING MOD(COUNT(cpr2.claimsts),2)=0 will work. Although I could use another column (I need a count of how many even numbered groups there are, or how many odd) the CLAIMSTS column documents the intent better (a status is paid or reversed, and can be flipped between the two up to a thousand times).
Basically I need the count of how many claims are in a paid state (odd number of entries) or a reversed state (even number of entries) where the claim started its life on a given date.

Ahhhh... So you're a victim of MySQL doing what you told it to instead of what you wanted!

The GROUP BY in your SELECT causes it to group the rows in the result set based on the cpr2.rxclaimnbr value. When you do a COUNT DISTINCT within one of those groups, of course the answer is 1 as long as the cpr2.rxclaimnbr isn't NULL.

MySQL doesn't handle sophisticated queries like this very well yet. The simplest answer is to have the client side code count the rows for you. The next best answer is to put the results of the cpr2.rxclaimnbr and Count(*) into a temporary table, and manipulate the values from there. I can't see a way to get the present incarnations of MySQL to do what you want in a single query, although other database engines can do it.

-PatP

Well, we plan on moving to Firebird or PostgresQL as soon as possible. What would the correct query be?
FWIW, since we're using J2EE I simply call resultSet.last() and then get the row number. The only problem is that this pulls the whole result set across the network.

The quick answer would be to do a subselect, such as
select count(*) from select ...
Is there a better way then a subselect?

I don't think that FireBird will help, but with PostgreSQL you can just make your existing SELECT statement a virtual table, so you could use something like:This will count the number of rows returned by the original SELECT statement.

-PatP