Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 445E59FA12B for ; Tue, 31 Oct 2006 23:54:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 13867-07 for ; Wed, 1 Nov 2006 03:54:42 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50809.mail.yahoo.com (web50809.mail.yahoo.com [206.190.38.252]) by postgresql.org (Postfix) with SMTP id B720B9F9F80 for ; Tue, 31 Oct 2006 23:54:41 -0400 (AST) Received: (qmail 76571 invoked by uid 60001); 1 Nov 2006 03:54:40 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=DEDtfVZkG6EKeRYyIwabky5si2wzauJulb3fBRzlMBHFLP5INTAuoXGEN91ugy4i0l9YIjUMLtnL+EV+udIUM0e54/fOxAfpxdRiFm3c/uiZqiJiYYTmb+lXECaZcFp7SCbnttIdOl6e0OWF5VES+rREC26LjxARd/68YXuMf2g= ; Message-ID: <20061101035440.76569.qmail@web50809.mail.yahoo.com> Received: from [203.134.174.70] by web50809.mail.yahoo.com via HTTP; Tue, 31 Oct 2006 19:54:40 PST Date: Tue, 31 Oct 2006 19:54:40 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org, Richard Broersma Jr In-Reply-To: <404822.35369.qm@web31809.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-863165699-1162353280=:76534" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.757 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200610/263 X-Sequence-Number: 26652 --0-863165699-1162353280=:76534 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Richard, Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I am able to get the result for a particular ric. Can you help me with getting the result for all the rics in the ticks table Thanks Roopa Richard Broersma Jr wrote: > 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. > > I really appreciate your help. Sorry I prematurely sent my first email Select foo.ric, date_trunc("minute", tm.times_time) as time_tick, count(tk.*) tickperminute, avg(tk.price), ... --your other aggregate functions from ( select ric from ticks where ric = 'A' group by ric ) as foo join ticks tk on (tk.ric = foo.ric) right join times tm on (tk.tick_time >= tm.times_time) and (tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and (tk.ric = 'A') -- this shouldn't be neccessary -- if you restructor your join -- since foo limits all ric to 'A' -- but since it is on the wrong side -- of an outer join it can't. group by foo.ric, time_tick order by time_tick; Regards, Richard Broersma Jr. --------------------------------- Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates. --0-863165699-1162353280=:76534 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi Richard,
 
Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I am able to get the result for a particular ric.
 
Can you help me with getting the result for all the rics in the ticks table
 
Thanks
Roopa

Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> 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.
>
> I really appreciate your help.

Sorry I prematurely sent my first email

Select foo.ric,
date_trunc("minute", tm.times_time) as time_tick,
count(tk.*) tickperminute,
avg(tk.price),
... --your other aggregate functions
from (
select ric
from ticks
where ric = 'A'
group by ric
) as foo
join ticks tk
on (tk.ric = foo.ric)
right join times tm
on (tk.tick_time >= tm.times_time)
and (tk.tick_time < (tm.times_time + '1 minute' :: interval)::time
and (tk.ric = 'A') -- this shouldn't be neccessary
-- if you restructor your join
-- since foo limits all ric to 'A'
-- but since it is on the wrong side
-- of an outer join it can't.
group by foo.ric,
time_tick

order by time_tick;

Regards,

Richard Broersma Jr.

Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates. --0-863165699-1162353280=:76534--