DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:from:to:content-type:content-transfer-encoding:mime-version:subject:date:x-mailer;
	b=nLOE7AoDN32B7CL6/yzxOHKvw72eBeBIh7x+cm4hvhJgMyeMiTv88QQLP5ZNAcajCshOxBz9Oxfu/uirvTDLixcQkWlum3E/XP1XLJOOL7X3/YzvlrH1QNnK0jTG+ar31QjyYYVV6oFwWOLBQPSpJa9uUzNNBtAxAMRupUnH3fg=
Message-Id: <824FF9F9-9997-4AD9-9955-3CA658E150D4@gmail.com>
From: Alexy Khrabrov <deliverable@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: columns for count histograms of values
Date: Wed, 30 Apr 2008 14:01:13 -0700

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