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