Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 777AB9FB2E5 for ; Sun, 29 Oct 2006 22:50:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 41789-02 for ; Sun, 29 Oct 2006 22:50:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50803.mail.yahoo.com (web50803.mail.yahoo.com [206.190.38.112]) by postgresql.org (Postfix) with SMTP id 0CF3F9FB2D2 for ; Sun, 29 Oct 2006 22:50:50 -0400 (AST) Received: (qmail 56771 invoked by uid 60001); 30 Oct 2006 02:50:49 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=vdSPA1+2iJA10Q6w56aZn3Wr/VEnXTkncQDWsjny5VJBDJ14eIHDvLCF0AAhxCmQfAQ6fQcHDNXqMC7ludmQRxXropxtXxPKVk+S3yhX0YpmTkU3WDRhnMxxzmLeQFRQg+LkLRW3wh9N7O/CIVdamVz/l48I0bhdUE7cDjmGYp8= ; Message-ID: <20061030025049.56769.qmail@web50803.mail.yahoo.com> Received: from [203.134.174.70] by web50803.mail.yahoo.com via HTTP; Sun, 29 Oct 2006 18:50:49 PST Date: Sun, 29 Oct 2006 18:50:49 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org Cc: Richard Broersma Jr In-Reply-To: <20061026175934.78136.qmail@web31806.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1836043908-1162176649=:42654" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.799 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200610/234 X-Sequence-Number: 26623 --0-1836043908-1162176649=:42654 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Thanks a lot for your help. The query does work, but now I have a problem. The query goes like this: select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time, tk.ric order by tk.ric, tm.timeseries_time The problem is, if there is no row for certain minute, then I want the count to be displayed as zero and other coulmns like avg to be null. In this query, it just omits those minutes which doesnt have any row for a particular minute. Thanks a lot in advance Roopa Richard Broersma Jr wrote: > I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries > table has fields like ticker, time, avg_price, avg_volume. > > The time field in Timeseries table is different from time in tick table, its the timeseries > for every minute. Now I want to calculate the average price & volume from tick table for each > ticker and for every minute and add those fields to timeseries table. Can anyone please help me > out with the sql query. > > Note: The ticker in the tick table also has duplicate values, so i am not able to create > relation between two tables. Here is my guess how it can be done: insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ... where select .... would be select tick, date_trunc('minute', time) as minute, avg(price) as avg_price, avg(volume) as avg_volume from ticker where time between 'yourstartdate' and 'yourenddate' group by tick, minute; Regards, Richard Broersma Jr. --------------------------------- Get your email and see which of your friends are online - Right on the new Yahoo.com --0-1836043908-1162176649=:42654 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi
 
Thanks a lot for your help. The query does work, but now I have a problem. The query goes like this:
 
select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)
from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and
tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time, tk.ric order by tk.ric, tm.timeseries_time
 
The problem is, if there is no row for certain minute, then I want the count to be displayed as zero and other coulmns like avg to be null. In this query, it just omits those minutes which doesnt have any row for a particular minute.
 
Thanks a lot in advance
Roopa

Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table, its the timeseries
> for every minute. Now I want to calculate the average price & volume from tick table for each
> ticker and for every minute and add those fields to timeseries table. Can anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not able to create
> relation between two tables.

Here is my guess how it can be done:

insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...

where select .... would be

select tick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;

Regards,

Richard Broersma Jr.

Get your email and see which of your friends are online - Right on the new Yahoo.com --0-1836043908-1162176649=:42654--