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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > >To: Dave Torr <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED] > >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 <[EMAIL PROTECTED]> > > > >To: Dave Torr <[EMAIL PROTECTED]> > > > >CC: [EMAIL PROTECTED] > > > >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/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
