Ow Mun Heng wrote:
=> select code, subset, avg(value) from foo group by subset, code;
code | subset | avg
------+--------+---------------------
A | 3 | 98.0000000000000000
A | 1 | 20.0000000000000000
A | 4 | 98.0000000000000000
A | 0 | 98.0000000000000000
A | 2 | 98.0000000000000000
An alternative way to get the output below, would be to feed your
aggregate query above to the crosstab() function in contrib/tablefunc.
Joe
=> select code, round(avg(case when subset = '0' then value else null
end),0) as v0,
round(avg(case when subset = '1' then value else null end),0) as v1,
round(avg(case when subset = '2' then value else null end),0) as v2,
round(avg(case when subset = '3' then value else null end),0) as v3,
round(avg(case when subset = '4' then value else null end),0) as v4
from foo
group by code;
code | v0 | v1 | v2 | v3 | v4
------+----+----+----+----+----
A | 98 | 20 | 98 | 98 | 98
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
