Re: selects on differing subsets of a query - Mailing list pgsql-sql

From Bruno Wolff III
Subject Re: selects on differing subsets of a query
Date
Msg-id 20060503144303.GB3774@wolff.to
Whole thread Raw
In response to selects on differing subsets of a query  (ed.temp.01@gmail.com)
List pgsql-sql
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.


pgsql-sql by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: selects on differing subsets of a query
Next
From: Everton Luís Berz
Date:
Subject: Re: Sorting aggregate column contents