pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: SQL problem with aggregate functions.
5+ messages / 5 participants
[nested] [flat]
* Re: SQL problem with aggregate functions.
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
1 sibling, 1 reply; 5+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: david.bouriaud@ac-rouen.fr; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* SQL problem with aggregate functions.
@ 2002-07-09 08:36 David BOURIAUD <david.bouriaud@ac-rouen.fr>
0 siblings, 2 replies; 5+ messages in thread
From: David BOURIAUD @ 2002-07-09 08:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL problem with aggregate functions.
@ 2002-07-11 22:08 Loyd Goodbar <loyd@blackrobes.net>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 5+ messages in thread
From: Loyd Goodbar @ 2002-07-11 22:08 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL problem with aggregate functions.
@ 2002-07-12 14:28 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Loyd Goodbar <loyd@blackrobes.net>
0 siblings, 0 replies; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-07-12 14:28 UTC (permalink / raw)
To: loyd@blackrobes.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL problem with aggregate functions.
@ 2002-07-22 08:26 Hubert depesz Lubaczewski <depesz@depesz.pl>
parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
1 sibling, 0 replies; 5+ messages in thread
From: Hubert depesz Lubaczewski @ 2002-07-22 08:26 UTC (permalink / raw)
To: David BOURIAUD <david.bouriaud@ac-rouen.fr>; +Cc: pgsql-sql
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:
select
distinct field_group,
(select count(*) from table t2 where t2.field_group = t1.field_group and t2.field='D') as count_of_d,
(select count(*) 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
from
table;
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-07-22 08:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-09 08:36 SQL problem with aggregate functions. David BOURIAUD <david.bouriaud@ac-rouen.fr>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2002-07-11 22:08 ` Loyd Goodbar <loyd@blackrobes.net>
2002-07-12 14:28 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-07-22 08:26 ` Hubert depesz Lubaczewski <depesz@depesz.pl>
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