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