Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 5D7DB9FB278 for ; Wed, 25 Oct 2006 22:59:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 51878-01 for ; Thu, 26 Oct 2006 01:59:54 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 3F1DB9FA6B5 for ; Wed, 25 Oct 2006 22:59:53 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so234366ugc for ; Wed, 25 Oct 2006 18:59:52 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=URkatvt9lYDS0ePTQcTgnOjsGapLIWhbr66YJtBEZT8OhvwO2FMLx5pR6trUBJk4AN0uQwnTWZIfcItCL1dNOhkfCaI3ZsP+UL9Dnh9VWTSJ44K3GokM734IVbD2GL2sv5yc+V2lbA5qHOBlylqZ5Nkxbj3xPry1jnpODvMhdKM= Received: by 10.78.185.16 with SMTP id i16mr1955898huf; Wed, 25 Oct 2006 18:59:51 -0700 (PDT) Received: by 10.78.136.6 with HTTP; Wed, 25 Oct 2006 18:59:51 -0700 (PDT) Message-ID: Date: Wed, 25 Oct 2006 20:59:51 -0500 From: "Aaron Bono" To: "roopa perumalraja" Subject: Re: Add calculated fields from one table to other table Cc: pgsql-sql@postgresql.org In-Reply-To: <20061026010736.67473.qmail@web50815.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22847_18528086.1161827991810" References: <20061026010736.67473.qmail@web50815.mail.yahoo.com> X-Google-Sender-Auth: 9a817a083cc210c7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/209 X-Sequence-Number: 26598 ------=_Part_22847_18528086.1161827991810 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/25/06, roopa perumalraja wrote: > > Hi > > 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. > Will this help: select ticker, date_trunc('minute', time), ave(price), ave(volume) from tick group by ticker, date_trunc('minute', time) You say you want to "add" these values to the Timeseries table? You mean insert them? If so do this: insert into timeseries ( ticker, time, avg_price, avg_volume ) select ticker, date_trunc('minute', time), ave(price), ave(volume) from tick group by ticker, date_trunc('minute', time) Of course if you do this repeatedly, you will start gathering duplicates in the timeseries so you may want to do one insert and one update: insert into timeseries ( ticker, time, avg_price, avg_volume ) select tick.ticker, date_trunc('minute', tick.time), ave(tick.price), ave(tick.volume) from tick left outer join timeseries on ( -- Not sure your join since you said time is not the same between ticke and timeseries date_trunc('minute', tick.time) = timeseries.tick and tick.ticker = timeseries.ticker ) group by ticker, date_trunc('minute', time) having timeseries.ticker is null ... I will leave the update as an exercise ;) ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_22847_18528086.1161827991810 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/25/06, roopa perumalraja <roopabenzer@yahoo.com> wrote:
Hi
 
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.

Will this help:

select
    ticker,
    date_trunc('minute', time),
    ave(price),
    ave(volume)
from tick
group by
    ticker,
    date_trunc('minute', time)

You say you want to "add" these values to the Timeseries table?  You mean insert them?  If so do this:

insert into timeseries (
ticker, time, avg_price, avg_volume
)
select
    ticker,
    date_trunc('minute', time),
    ave(price),
    ave(volume)
from tick
group by
    ticker,
    date_trunc('minute', time)

Of course if you do this repeatedly, you will start gathering duplicates in the timeseries so you may want to do one insert and one update:

insert into timeseries (
ticker, time, avg_price, avg_volume
)
select
    tick.ticker,
    date_trunc('minute', tick.time),
    ave(tick.price),
    ave(tick.volume)
from tick
left outer join timeseries on (
    -- Not sure your join since you said time is not the same between ticke and timeseries
    date_trunc('minute', tick.time) = timeseries.tick
    and tick.ticker = timeseries.ticker
)
group by
    ticker,
    date_trunc('minute', time)
having timeseries.ticker is null

... I will leave the update as an exercise ;)

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_22847_18528086.1161827991810--