I knew there was a better way than what I suggested! Of course, you'll want
to use your actual query for the first line:
SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c > 1;
SELECT FOUND_ROWS();
Michael
Garth Webb wrote:
> You could also try:
>
> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
> SELECT FOUND_ROWS();
>
> On Mon, 2004-06-14 at 20:41, Dave Torr wrote:
>
>>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 (c) 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....
>>>>
>>>>
>>>>
>>>
>>>
>>
>
