Results 1 to 3 of 3
  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
    Mar 2004
    Posts
    480
    I think this is all you need:

    Code:
    select cpr2.rxclaimnbr, count(*)
    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

  3. #3
    Join Date
    Jun 2004
    Location
    Florida
    Posts
    7
    This generates 50 rows, with the first column the claim number, and the second the number of rows in each group.

Posting Permissions

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