From: Markus Schaber <schabi@logix-tt.com>
To: ed.temp.01@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: selects on differing subsets of a query
Date: Wed, 03 May 2006 12:40:01 +0200
Message-ID: <44588881.5070202@logix-tt.com> (raw)
In-Reply-To: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
References: <306f0afd0605030147i598ba049q9ef55a3f3b08847c@mail.gmail.com>
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: schabi@logix-tt.com, ed.temp.01@gmail.com
Subject: Re: selects on differing subsets of a query
In-Reply-To: <44588881.5070202@logix-tt.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