pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcolumns for count histograms of values
2+ messages / 2 participants
[nested] [flat]
* columns for count histograms of values
@ 2008-04-30 21:01 Alexy Khrabrov <deliverable@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Alexy Khrabrov @ 2008-04-30 21:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: columns for count histograms of values
@ 2008-04-30 23:12 chester c young <chestercyoung@yahoo.com>
parent: Alexy Khrabrov <deliverable@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: chester c young @ 2008-04-30 23:12 UTC (permalink / raw)
To: Alexy Khrabrov <deliverable@gmail.com>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-04-30 23:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-30 21:01 columns for count histograms of values Alexy Khrabrov <deliverable@gmail.com>
2008-04-30 23:12 ` chester c young <chestercyoung@yahoo.com>
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