DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=m4TUk1rjeYNNpnhbcdqcc8HrI/2rkvEqzhpJXOFniXqA8vKB7zKWC6ojHIt1c7iQdVFDH10HMllipiUDJdxreRCatN0S84E0FA9wpzCPojXaKP24w27cIU8s2/dTEqUekK5jfNc+0I27eKVarB0Ljsrs9p0QG7p+bHmRbnRaOA4=
	;
Date: Tue, 31 Oct 2006 05:27:32 -0800 (PST)
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: <20061031065418.10959.qmail@web50802.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <554162.29041.qm@web31803.mail.mud.yahoo.com>

>   Thanks for your help. That does make sense, but I am not able to get the result what I wanted
> exactly. Let me explain you.
>    
>   I have ticks table in which I have columns like ric, tick_time, price & volume. The times
> table has just one column with times_time which has time data for each minute ie.) 
>    
>   Ticks
>   ric | tick_time | price | volume
>   A | 12:00:01 | 23.00 | 12
>   A | 12:00:02 | 26.00 | 7
>   B | 12: 00:02 | 8.00 | 2
>   B | 12:01:01 | 45.00 | 6
>    
>   Times
>   times_time
>   12:00
>   12:01
>   12:02
>    
>   Now I want the timeseries for each minute for all ric in the tick table. So my query goes like
> this for a particular ric say for example ric 'A'
>    
>   select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'A' group by
> tm.times_time, foo.ric order by tm.times_time; 
> 
>   I get the result as I expect, but i am not able to derive a query for all rics in the tick
> table.
>    

How about:

SELECT 
         foo.ric,
         date_trunc('minute', tm.times_time) as minute,
         count(tk.*),