Message-ID: <3D2EE781.597DB3FA@nsd.ca>
Date: Fri, 12 Jul 2002 10:28:17 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: loyd@blackrobes.net
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL problem with aggregate functions.
References: <200207091036.17913.david.bouriaud@ac-rouen.fr>
	<200207110837.KAA07881@rodos>
	<q60siuo880dl7qflpur9tdoel98qvk31oi@4ax.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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