Thread: columns for count histograms of values
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
Just curious, how are you planning to display the histogram? <br />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 youcan show.<br /><br /><br /><br /><div class="gmail_quote">On Wed, Apr 30, 2008 at 5:01 PM, Alexy Khrabrov <<a href="mailto:deliverable@gmail.com">deliverable@gmail.com</a>>wrote:<br /><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Greetings -- I have a tableof the kind<br /><br /> Ratings:<br /> id integer<br /> rating smallint<br /><br /> -- where value can take any valuein the range 1 to 5. Now I want to have a statistical table Stats of the form<br /><br /> id integer<br /> min smallint<br/> max smallint<br /> avg real<br /> r1 integer<br /> r2 integer<br /> r3 integer<br /> r4 integer<br /> r5 integer<br/><br /> -- how can I create it in one pass over Ratings? I can use min(), max(), avg() for<br /> insert intostats values (id,select min(rating), max(rating), avg(rating), ...) from ratings<br /><br /> -- but what to do for r1,..,r5,short of subselects (select count(rating) from ratings where stats.id=<a href="http://ratings.id" target="_blank">ratings.id</a>)for each, which is an overkill?<br /><br /> Also, if a table Stats already exists with somemore columns, and we need to do an update, not insert, for the above, how would that work --<br /><br /> update statsset min=min(ratings), ... from ratings where stats.id=<a href="http://ratings.id" target="_blank">ratings.id</a> --how do we do the histogram in this case, where the id is fixed explicitly?<br /><br /> Cheers,<br /> Alexy<br /><font color="#888888"><br/> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/>
--- Alexy Khrabrov <deliverable@gmail.com> 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?
select id, min(rating), max(rating), avg(rating), sum( case rating = 1 then 1 else 0 end ), ...
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Chester -- awesome! Exactly what the doctor ordered. Just one syntactic fix needed on 8.3.1: case when rating=1 then 1 else 0 end -- etc. Cheers, Alexy On Apr 30, 2008, at 4:12 PM, chester c young wrote: > > --- Alexy Khrabrov <deliverable@gmail.com> 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? > > select id, min(rating), max(rating), avg(rating), > sum( case rating = 1 then 1 else 0 end ), > ... > > > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ