DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Fdb/rWrXX8M2ZR81U9yPbQGOatnK+NbuVnTGmpB4Blp2J5L3qlQm1i0av2sILqDDkCr3CzjbRjzEP8vveWcvFFMD0RxXJbt1l5/wan32HBaG/TA6VkfazTaKD2OR0rcs0Pn9lMh8b+WOD6Fz4Oe/COO2lZgI2EKOZvAvaYBrN/A=
Message-ID: <306f0afd0605030416r63a4c8c8uea7e11f4ef513f20@mail.gmail.com>
Date: Wed, 3 May 2006 12:16:42 +0100
From: ed.temp.01@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: selects on differing subsets of a query
In-Reply-To: <44588881.5070202@logix-tt.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
	<44588881.5070202@logix-tt.com>

Hello Markus,

It's actually a temporary mailbox just in case the list attracts spam :-)

Thank you for your help, I will study it when I get development time
on the database.

On 03/05/06, Markus Schaber <schabi@logix-tt.com> wrote:
> Hi, Ed Temp,
>
> ed.temp.01@gmail.com wrote:
>
> > First post, be gentle as I have terminology problems and so the
> > subject might be wrongly worded.
>
> Hmm, the biggest mistake seems that "et temp 01" is a very unlikely real
> name, so you should reconfigure your mail client :-)
>
> > 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.
> >
> > As 2 queries this would be:
> > select count(*) from mytab where gender=3D'm'
> > select count(*) from mytab where gender=3D'm' and diet_pref=3D'veg'
>
> Have you tried to UNION ALL the two queries?
>
> > The table is big and I'd like to do the select where gender=3D'm' only
> > once. (In the actual situation the select is on a date range)
>
> SELECT count(*),diet_pref=3D'veg' FROM table WHERE gender=3D'm' GROUP BY
> diet_pref=3D'veg'
>
> Is not exactly what you want, as your application still has to add two
> numbers to get the total result, but avoids the duplicated table scan.
>
>
> SELECT count(*),count(nullif(diet_pref=3D'veg', f)) FROM table WHERE
> gender=3D'm'
>
> Should also give you both counts, this time in different columns, also
> avoiding the duplicated table scan. It relies on the fact that
> count(something) is only called if something is not null, whereas
> count(*) is called for every row (as a special case).
>
> HTH,
> Markus
>
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf.     | Software Development GIS
>
> Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.=
org
>