Date: Wed, 3 May 2006 09:43:03 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: ed.temp.01@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: selects on differing subsets of a query
Message-ID: <20060503144303.GB3774@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>, ed.temp.01@gmail.com,
	pgsql-sql@postgresql.org
References: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
User-Agent: Mutt/1.4.2.1i

On Wed, May 03, 2006 at 09:47:49 +0100,
  ed.temp.01@gmail.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.