DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=FLulx1T6oQlV/Y9Tb2y/nN3X8amB5YVV8kRyl7I9ng8Bzeh1IPnbXm4WxOrFpyy8X6GwfZZ9/a1qx2dHV8GxVr7jU7JjjVSZM7TJ7aaqi8H1ctJJhMhv+ydX3KufnI7AcdUUaUDaU/3txJmORs8Md5QEKJsVWUY0vMRO1BPuYy8=
	;
Date: Sun, 29 Oct 2006 19:12:51 -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
Cc: Richard Broersma Jr <rabroersma@yahoo.com>
In-Reply-To: <20061030025049.56769.qmail@web50803.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <534177.87743.qm@web31809.mail.mud.yahoo.com>

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

You have to use an outer join.  You will need a table or sequence that has every minute in a range
that you are interested in and outer join that to your actual table.  This will give you a count
of zero.

i.e.

select S.minute, count(W.minute) as minutecnt

from Series_of_Minutes S left join Working_table W

on S.minute = W.minute
;

hope this helps.

REgards,

Richard Broersma jr.

ps. sorry that my query suggestion didn't work :0)