Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Location
    Florida
    Posts
    7

    Unanswered: Problem with COUNT and HAVING

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You should be able to use:
    Code:
    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

  3. #3
    Join Date
    Jun 2004
    Location
    Florida
    Posts
    7
    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 :-)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You DID remove the GROUP BY clause, right?

    -PatP

  5. #5
    Join Date
    Jun 2004
    Location
    Florida
    Posts
    7
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Jun 2004
    Location
    Florida
    Posts
    7
    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.

  8. #8
    Join Date
    Jun 2004
    Location
    Florida
    Posts
    7
    The quick answer would be to do a subselect, such as
    select count(*) from select ...
    Is there a better way then a subselect?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    SELECT Count(*)
       FROM ( -- original SELECT goes here
    ) AS a
    This will count the number of rows returned by the original SELECT statement.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •