List:General Discussion« Previous MessageNext Message »
From:Brian Mansell Date:June 15 2004 9:24pm
Subject:Re: How to COUNT rows when they have a COUNT in them
View as plain text  
Dave -

I would recommend (if not done so already) adding an index on owner.
That should improve the speed quite a bit.

On Tue, 15 Jun 2004 21:06:47 +0000, Dave Torr <dave_torr@stripped> wrote:
> 
> Thanks - that is basically what I used to do (it works fine now on 4.1.2)
> but it was very slow as there are a LOT of rows and this method returned all
> of them.
> 
> >From: Garth Webb <garth@stripped>
> >To: Dave Torr <dave_torr@stripped>
> >CC: mysql@stripped
> >Subject: Re: How to COUNT rows when they have a COUNT in them
> >Date: Tue, 15 Jun 2004 09:54:19 -0700
> >
> >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....
> > > >>
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 
> >
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
>
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