Re: SQL problem with aggregate functions.

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