Re: selects on differing subsets of a query

On Wed, May 03, 2006 at 09:47:49 +0100,
ed(dot)temp(dot)01(at)gmail(dot)com wrote:
> First post, be gentle as I have terminology problems and so the
> subject might be wrongly worded.
>
> Say I have a table with fields
> ...
> gender
> diet_pref
> ...
>
> What I am trying to construct is a *single* query showing the total
> number of males in the table
> and also the total number of male vegetarians in the table, i.e. the
> 2nd value is computed on a subset of the records needed for the first
> value.

There are a few ways you could do this. One is to use a CASE function to
return 1 for diet_pref = 'veg' and 0 otherwise. Then you can do a count(*)
and a count of the CASE result in the same query and get both totals
with one pass through the table. Another option would be joining the two
queries. I don't think this is a good idea when you have to count everyone
anyway, but if you were counting a couple of small subsets of the data and
had partial indexes to speed those counts up, this might be a better strategy.