Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 7A8F09FB1E2 for ; Mon, 30 Oct 2006 19:20:01 -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 98530-05 for ; Mon, 30 Oct 2006 23:19:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50814.mail.yahoo.com (web50814.mail.yahoo.com [206.190.39.120]) by postgresql.org (Postfix) with SMTP id EC60B9FB1E6 for ; Mon, 30 Oct 2006 19:19:54 -0400 (AST) Received: (qmail 16121 invoked by uid 60001); 30 Oct 2006 23:19:53 -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=jSqMJHE8pqKhtSWakKAFUmkOWMAQL7IBMqVFLMiEyyNgzBIpYRn4EOaKB+4lnNrG6i5SagWVk97JmBopguTuw+06kw82vVgYNTvSNquV/sh0J3Cbb1nGF/qjBs57ka0ygjWkGEYi8XquBl4nIQwxNOH084SfUP4RkjH2XR+oHIw= ; Message-ID: <20061030231953.16119.qmail@web50814.mail.yahoo.com> Received: from [203.134.174.70] by web50814.mail.yahoo.com via HTTP; Mon, 30 Oct 2006 15:19:53 PST Date: Mon, 30 Oct 2006 15:19:53 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org Cc: Moiz Kothari In-Reply-To: <82e1a9bd0610292147q634cc985r33dd5c613d7d1ba2@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-349515138-1162250393=:15568" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.131 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_30_40, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200610/238 X-Sequence-Number: 26627 --0-349515138-1162250393=:15568 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Thanks a lot for your help. The query which you suggested gives me a result like this A | 12:00| 12 | 64.99 | 63.99 | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 but I wanted the result to look like this A | 12:00| 12 | 64.99 | 63.99 A | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 Can anybody help me with that. Thanks in advance Roopa Moiz Kothari wrote: 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 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. --------------------------------- Get your email and see which of your friends are online - Right on the new Yahoo.com --0-349515138-1162250393=:15568 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi
 
Thanks a lot for your help. The query which you suggested gives me a result like this
 
A  | 12:00| 12 | 64.99 | 63.99
    | 12:01 | 0 | |
A | 12:02 | 5 | 36.99 | 32.99
 
but I wanted the result to look like this
 
A  | 12:00| 12 | 64.99 | 63.99
A | 12:01 | 0 | |
A | 12:02 | 5 | 36.99 | 32.99

Can anybody help me with that.
 
Thanks in advance
Roopa

Moiz Kothari <moizpostgres@gmail.com> wrote:
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.


Get your email and see which of your friends are online - Right on the new Yahoo.com --0-349515138-1162250393=:15568--