List:General Discussion« Previous MessageNext Message »
From:Dave Torr Date:June 15 2004 1:58am
Subject:How to COUNT rows when they have a COUNT in them
View as plain text  
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