DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=L65zGRczRzTvlyP3SE8qmNMifnYgUkcsUFUy+pgQrocGoheCEGclF+xCT6CjDv41z6jgjaWpkPgmu9E0SMVJhO3Jocx8Jml+6O3jEebVwFYcRNBMwhngLmgoD59IvJzNKLRARfVvJheJJ7naYXSXMvyDZvlINCk2wQhoC5p19ns=
	;
Message-ID: <20061026175934.78136.qmail@web31806.mail.mud.yahoo.com>
Date: Thu, 26 Oct 2006 10:59:34 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Add calculated fields from one table to other table
To: roopa perumalraja <roopabenzer@yahoo.com>, pgsql-sql@postgresql.org
In-Reply-To: <20061026010736.67473.qmail@web50815.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

>   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.