Re: How to COUNT rows when they have a COUNT in them

[Michael Stassen]

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





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]