Just curious, how are you planning to display the histogram? Are you allowed to use application code (C/C++/Perl, etc..) to generate the histogram? Personally, SQL is great for showing the data but not good for making graphs with data you can show.
On Wed, Apr 30, 2008 at 5:01 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > 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 >
