List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 15 2004 7:03pm
Subject:Re: How to COUNT rows when they have a COUNT in them
View as plain text  
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....
>>>>
>>>>
>>>>
>>>
>>>
>>
> 

Thread
How to COUNT rows when they have a COUNT in themDave Torr15 Jun
  • Re: How to COUNT rows when they have a COUNT in themYayati Kasralikar15 Jun
Re: How to COUNT rows when they have a COUNT in themDave Torr15 Jun
  • Re: How to COUNT rows when they have a COUNT in themMichael Stassen15 Jun
  • Re: How to COUNT rows when they have a COUNT in themMartijn Tonies15 Jun
  • Re: How to COUNT rows when they have a COUNT in themGarth Webb15 Jun
    • Re: How to COUNT rows when they have a COUNT in themMichael Stassen15 Jun
Re: How to COUNT rows when they have a COUNT in themDave Torr15 Jun
  • Re: How to COUNT rows when they have a COUNT in themBrian Mansell15 Jun