Problem with COUNT and HAVING

rickd · #1 (**permalink**) 06-10-04, 10:49

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?

guelphdad · #2 (**permalink**) 06-10-04, 14:21

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

rickd · #3 (**permalink**) 06-10-04, 15:25

This generates 50 rows, with the first column the claim number, and the second the number of rows in each group.

Internet Services

Web Development

Digital Marketing

Consumer Tech