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
Date: Wed, 3 May 2006 09:43:03 -0500
Message-ID: <20060503144303.GB3774@wolff.to> (raw)
In-Reply-To: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
References: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, ed.temp.01@gmail.com
Subject: Re: selects on differing subsets of a query
In-Reply-To: <20060503144303.GB3774@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox