Thread: SQL problem with aggregate functions.

SQL problem with aggregate functions.

From

David BOURIAUD

Date:

09 July 2002, 09:43:06

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

Re: SQL problem with aggregate functions.

From

Christoph Haller

Date:

11 July 2002, 07:37:44

> 
> 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

Re: SQL problem with aggregate functions.

From

Loyd Goodbar

Date:

11 July 2002, 21:08:55

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/

Re: SQL problem with aggregate functions.

From

Jean-Luc Lachance

Date:

12 July 2002, 13:23:29

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

Re: SQL problem with aggregate functions.

From

Hubert depesz Lubaczewski

Date:

22 July 2002, 07:26:50

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