Re: selects on differing subsets of a query

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(at)logix-tt(dot)com> wrote:
> Hi, Ed Temp,
>
> ed(dot)temp(dot)01(at)gmail(dot)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
>