Thanks - this did not work for me as I am on 4.0.17 - presumably this works
on 4.1 (seems to need the SubQuery feature)? If so I will upgrade
immediately!
>From: Yayati Kasralikar <yayati@stripped>
>To: Dave Torr <dave_torr@stripped>
>CC: mysql@stripped
>Subject: Re: How to COUNT rows when they have a COUNT in them
>Date: Mon, 14 Jun 2004 23:37:15 -0400
>
>Following query does what you want:
>
>SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING
>c>1) as temp
>
>-Yayati
>
>Dave Torr wrote:
>
>>Probably simple but I can't figure it out!
>>
>>THe manual section 3.3.4.8 has the example
>>
>>SELECT owner, COUNT(*) FROM pet GROUP BY owner
>>
>>which is fine. Now what I want to do is count the number of rows this
>>returns. Actually of course this is trivial - I can just count how many
>>owners there are.
>>
>>What I actually have is something similar to
>>
>>SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c>1
>>
>>(ie I want to see the owners who have more than one pet). And I just want
>>to know how many there are - at the moment I am having to retreive the
>>full data set (which is large in my case).
>>
>>What I want is something like
>>
>>SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c>1)
>>
>>but that doesn't work....
>>
>>
>>
>
>
