From: Alexy Khrabrov <deliverable@gmail.com>
To: pgsql-sql@postgresql.org
Subject: columns for count histograms of values
Date: Wed, 30 Apr 2008 14:01:13 -0700
Message-ID: <824FF9F9-9997-4AD9-9955-3CA658E150D4@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: deliverable@gmail.com
Subject: Re: columns for count histograms of values
In-Reply-To: <824FF9F9-9997-4AD9-9955-3CA658E150D4@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox