[rickd]

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?

[Pat Phelan]

You should be able to use:

SELECT Count(DISTINCT cpr2.rxclaimnbr)
   FROM claimspaidreversed as cpr1
   JOIN claimspaidreversed as cpr2 on (cpr1.rxclaimnbr=cpr2.rxclaimnbr)
   WHERE cpr1.datesbm='2004-06-04'

-PatP

[rickd]

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 :-)

[Pat Phelan]

You DID remove the GROUP BY clause, right?

-PatP

[rickd]

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.

[Pat Phelan]

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

[rickd]

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.

[rickd]

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

[Pat Phelan]

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:

SELECT Count(*)
   FROM ( -- original SELECT goes here
) AS a

This will count the number of rows returned by the original SELECT statement.

-PatP