From: ed.temp.01@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: selects on differing subsets of a query
Date: Wed, 3 May 2006 12:16:42 +0100
Message-ID: <306f0afd0605030416r63a4c8c8uea7e11f4ef513f20@mail.gmail.com> (raw)
In-Reply-To: <44588881.5070202@logix-tt.com>
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='m'
> > select count(*) from mytab where gender='m' and diet_pref='veg'
>
> Have you tried to UNION ALL the two queries?
>
> > The table is big and I'd like to do the select where gender='m' only
> > once. (In the actual situation the select is on a date range)
>
> SELECT count(*),diet_pref='veg' FROM table WHERE gender='m' GROUP BY
> diet_pref='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='veg', f)) FROM table WHERE
> gender='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
>
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: ed.temp.01@gmail.com
Subject: Re: selects on differing subsets of a query
In-Reply-To: <306f0afd0605030416r63a4c8c8uea7e11f4ef513f20@mail.gmail.com>
* 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