Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BF9049FA4C3 for ; Mon, 30 Oct 2006 01:34:33 -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 42854-06-2 for ; Mon, 30 Oct 2006 01:34:28 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50813.mail.yahoo.com (web50813.mail.yahoo.com [206.190.39.119]) by postgresql.org (Postfix) with SMTP id B46699FA50F for ; Mon, 30 Oct 2006 01:34:27 -0400 (AST) Received: (qmail 67641 invoked by uid 60001); 30 Oct 2006 05:34:26 -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=cvFPzJKHIxwauKgguPXAtMt3in9dEitUwTeQ8MoyQbk8IO0p7JR24x2HhTTgObfoFO1rG7UyyjMM2H8sJFcrFv4CBChveQds67IQhjLJuF4WIQRz4H7laVBaUi7+ZZpTLnpYViA1jJz0PPX4/jAdEglpqYgxx/2jLZZjnFPEsRU= ; Message-ID: <20061030053426.67639.qmail@web50813.mail.yahoo.com> Received: from [203.134.174.70] by web50813.mail.yahoo.com via HTTP; Sun, 29 Oct 2006 21:34:26 PST Date: Sun, 29 Oct 2006 21:34:26 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org Cc: orgRichard Broersma Jr In-Reply-To: <534177.87743.qm@web31809.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-846898232-1162186466=:67527" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.778 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200610/236 X-Sequence-Number: 26625 --0-846898232-1162186466=:67527 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Thanks a lot for your immediate reply. I want to explain more about it. The ticks table has many rows in each minute and timeseries table has 1 minute increment data. And the query is as mentioned below, which just displays the result for the minutes in which the tick data exists. but i would like the result for the query to be like ric | time | count | avg_price | avg_volume A | 12:00| 12 | 64.99 | 63.99 A | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 but my query result is just A | 12:00| 12 | 64.99 | 63.99 A | 12:02 | 5 | 36.99 | 32.99 so can you help me out to modify the query to get the result what I expect 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 Thanks a lot in advance Roopa ps. I wrote the query from your idea. so tanks a lot Richard Broersma Jr wrote: > 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) --------------------------------- Want to start your own business? Learn how on Yahoo! Small Business. --0-846898232-1162186466=:67527 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi
 
Thanks a lot for your immediate reply. I want to explain more about it. The ticks table has many rows in each minute and timeseries table has 1 minute increment data. And the query is as mentioned below, which just displays the result for the minutes in which the tick data exists. but i would like the result for the query to be like
 
ric | time | count | avg_price | avg_volume
A  | 12:00| 12 | 64.99 | 63.99
A | 12:01 | 0 | |
A | 12:02 | 5 | 36.99 | 32.99
 
but my query result is just
 
A  | 12:00| 12 | 64.99 | 63.99
A | 12:02 | 5 | 36.99 | 32.99
 
so can you help me out to modify the query to get the result what I expect
 
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
Thanks a lot in advance
Roopa
 
ps. I wrote the query from your idea. so tanks a lot

Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> 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)

Want to start your own business? Learn how on Yahoo! Small Business. --0-846898232-1162186466=:67527--