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