Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A1AE99FB26B for ; Mon, 30 Oct 2006 01:47:28 -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 56998-01 for ; Mon, 30 Oct 2006 01:47:24 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 160149FB21B for ; Mon, 30 Oct 2006 01:47:23 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so915813ugc for ; Sun, 29 Oct 2006 21:47:23 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=eu7IlxQIAxMY/2fAT7ahl2av8oWElYh5HBa1Xy0eimA13NuIcf6osU8z4/wHmCW5vKDg7JN3Jj+9KKBCiG2nWvzq2ANKWsR0XZ0Vz9MznWN9dbrN7TTkb2HnCeM42RFXbkrc3n2s7Sus/zGkMdAKwKBqb1MkykPiuUTjCxQe58c= Received: by 10.67.27.3 with SMTP id e3mr3607679ugj; Sun, 29 Oct 2006 21:47:22 -0800 (PST) Received: by 10.67.26.19 with HTTP; Sun, 29 Oct 2006 21:47:22 -0800 (PST) Message-ID: <82e1a9bd0610292147q634cc985r33dd5c613d7d1ba2@mail.gmail.com> Date: Mon, 30 Oct 2006 11:17:22 +0530 From: "Moiz Kothari" To: "roopa perumalraja" Subject: Re: Add calculated fields from one table to other table Cc: pgsql-sql@postgresql.org, "orgRichard Broersma Jr" In-Reply-To: <20061030053426.67639.qmail@web50813.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_90280_18498842.1162187242686" References: <534177.87743.qm@web31809.mail.mud.yahoo.com> <20061030053426.67639.qmail@web50813.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/237 X-Sequence-Number: 26626 ------=_Part_90280_18498842.1162187242686 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Roopa, If your timeseries table has records for all minutes, then you should outer join both tables so as to get the desired results you are looking for... try doing 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 right outer join 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 On 10/30/06, roopa perumalraja wrote: > > 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_timeand > > 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. > > ------=_Part_90280_18498842.1162187242686 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Roopa,

If your timeseries table has records for all minutes, then you should outer join both tables so as to get the desired results you are looking for... try doing 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 right outer join 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

On 10/30/06, roopa perumalraja < roopabenzer@yahoo.com> wrote:
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.


------=_Part_90280_18498842.1162187242686--