Обсуждение: SQL problem with aggregate functions.
Hi the list ! 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 ? -- David BOURIAUD ---------------------------------------------------------- In a world without walls or fences, what use do we have for windows or gates ? ---------------------------------------------------------- ICQ#102562021
> > 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, CASEWHEN f1 = 'X' THEN 1 ELSE 0 END AS f1_xFROM tab ) AS foo ; Regards, Christoph
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/
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
On Tue, Jul 09, 2002 at 10:36:17AM +0200, David BOURIAUD wrote:
> Field group | count of D | count of R | count of X.
if you want this that way, i suggest using subselects.
like:
selectdistinct field_group,(select count(*) from table t2 where t2.field_group = t1.field_group and t2.field='D') as
count_of_d,(selectcount(*) from table t2 where t2.field_group = t1.field_group and t2.field='R') as count_of_r,(select
count(*)from table t2 where t2.field_group = t1.field_group and t2.field='X') as count_of_x
fromtable;
should work the way you want it.
anyway, i belive that making this:
select field_group, field, count(*) from table where field in
('D','R','X') group by field_group, field;
and then processing results in client application, should be a little
bit better/faster solution.
depesz
--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
coś do powiedzenia. (c) 1998 depesz