List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 15 2004 4:04am
Subject:Re: How to COUNT rows when they have a COUNT in them
View as plain text  
Right, subqueries require 4.1.  In 4.0.17, you could do this with 2 queries 
and a temporary table:

   CREATE TEMPORARY TABLE owners_temp SELECT COUNT(*) as c
     FROM pet GROUP BY owner HAVING c>1;

   SELECT COUNT(*) FROM owners_temp;
   DROP TABLE owners_temp;

There may be a better way, but that should work.

Michael

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 (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....
>>>
>>>
>>>
>>
>>
> 
> 
> 

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