From: Jean-Luc Lachance <jllachan@nsd.ca>
To: loyd@blackrobes.net
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL problem with aggregate functions.
Date: Fri, 12 Jul 2002 10:28:17 -0400
Message-ID: <3D2EE781.597DB3FA@nsd.ca> (raw)
References: <200207091036.17913.david.bouriaud@ac-rouen.fr>
<200207110837.KAA07881@rodos>
<q60siuo880dl7qflpur9tdoel98qvk31oi@4ax.com>
What is wrong with:
select field_group, sum( case when f1 = 'D' then cnt else 0 end) as
D_COUNT,
sum( case when f1 = 'R' then cnt else 0 end) as R_COUNT,
sum( case when f1 = 'X' then cnt else 0 end) as X_COUNT
from (select field_group, f1, count (*) as cnt from tab group by
field_group, f1) as ss
group by field_group;
It should be faster because there is less CASE evaluation.
Loyd Goodbar wrote:
>
> I would suggest something like
>
> select sum(case when f1 = 'D' then 1 else 0 end) as D_COUNT,
> sum(case when f1 = 'R' then 1 else 0 end) as R_COUNT,
> sum(case when f1 = 'X' then 1 else 0 end) as X_COUNT
> from tab
> where f1 in ('D','R','X')
>
> Not sure what the "field group" represents.
>
> HTH,
> Loyd
>
> On Thu, 11 Jul 2002 10:37:40 METDST, Christoph Haller <ch@rodos.fzk.de> wrote:
>
> >>
> >> I've got a table in which there is a field that can have one amongst 3
> >> possible values : D, R, X. Is it possible to get in one query the count of
> >> this different values.Please, note that I don't want to have a querry like
> >> this :
> >> "select count (*) from tab group by f1;", cause i want to get all the possible
> >> count values in one row (these data are already grouped on another field).
> >> To give a more accurate example, here is what I want to retrieve :
> >>
> >> Field group | count of D | count of R | count of X.
> >>
> >> Any clues ?
> >> --
> >What about something like
> >
> >
> >SELECT SUM(f1_d) AS count_d,
> > SUM(f1_r) AS count_r,
> > SUM(f1_x) AS count_x
> >FROM (
> > SELECT CASE WHEN f1 = 'D' THEN 1 ELSE 0 END AS f1_d,
> > CASE WHEN f1 = 'R' THEN 1 ELSE 0 END AS f1_r,
> > CASE WHEN f1 = 'X' THEN 1 ELSE 0 END AS f1_x
> > FROM tab ) AS foo ;
> >
> >Regards, Christoph
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
>
> --
> "Why, you can even hear yourself think." --Hobbes
> "This is making me nervous. Let's go in." --Calvin
> loyd@blackrobes.net ICQ#504581 http://www.blackrobes.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.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: jllachan@nsd.ca, loyd@blackrobes.net
Subject: Re: SQL problem with aggregate functions.
In-Reply-To: <3D2EE781.597DB3FA@nsd.ca>
* 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