Greetings -- I have a table of the kind

Ratings:
id integer
rating smallint

-- where value can take any value in the range 1 to 5. Now I want to
have a statistical table Stats of the form

id integer
min smallint
max smallint
avg real
r1 integer
r2 integer
r3 integer
r4 integer
r5 integer

-- how can I create it in one pass over Ratings? I can use min(),
max(), avg() for

insert into stats values (id,select min(rating), max(rating),
avg(rating), ...) from ratings

-- but what to do for r1,..,r5, short of subselects (select
count(rating) from ratings where stats.id=ratings.id) for each, which
is an overkill?

Also, if a table Stats already exists with some more columns, and we
need to do an update, not insert, for the above, how would that work --

update stats set min=min(ratings), ... from ratings where
stats.id=ratings.id -- how do we do the histogram in this case, where
the id is fixed explicitly?

Cheers,
Alexy

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql