Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id A8EED9FB311 for ; Tue, 31 Oct 2006 02:06:34 -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 16733-07 for ; Tue, 31 Oct 2006 06:06:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 0327C9FB257 for ; Tue, 31 Oct 2006 02:06:29 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so1191468ugc for ; Mon, 30 Oct 2006 22:06:28 -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=UoANva3It1xoX1Si0LSCUetVYhJog6XAYtJVMZ8U2igRS5r+srk9TzejCbTLmwLOqQUrk6sxDjNa/9xqTiw8wJLdlWh5nVhv9eTEtwKnNM+G+6xXyBCU0xfAn8Yte7Ow1pCB9A7AaEsSO+UlfjVoAsdFmH5IXov7/GuRVQ+sPis= Received: by 10.66.244.10 with SMTP id r10mr5550778ugh; Mon, 30 Oct 2006 22:06:28 -0800 (PST) Received: by 10.67.26.19 with HTTP; Mon, 30 Oct 2006 22:06:28 -0800 (PST) Message-ID: <82e1a9bd0610302206j2b2f5725pc0b66204127fcff3@mail.gmail.com> Date: Tue, 31 Oct 2006 11:36:28 +0530 From: "Moiz Kothari" To: "roopa perumalraja" Subject: Re: Add calculated fields from one table to other table Cc: pgsql-sql@postgresql.org In-Reply-To: <20061030231953.16119.qmail@web50814.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2017_31975457.1162274788351" References: <82e1a9bd0610292147q634cc985r33dd5c613d7d1ba2@mail.gmail.com> <20061030231953.16119.qmail@web50814.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/243 X-Sequence-Number: 26632 ------=_Part_2017_31975457.1162274788351 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Roopa, Why dont you try putting in some case or decode with your first field, so incase if nothing is returned you explicitly make it 'A' kinds. Regards, Moiz Kothari On 10/31/06, roopa perumalraja wrote: > > 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_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 > > 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. > > > > > > > ------------------------------ > Get your email and see which of your friends are online - Right on the new > Yahoo.com > > ------=_Part_2017_31975457.1162274788351 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Roopa,

Why dont you try putting in some case or decode with your first field, so incase if nothing is returned you explicitly make it 'A' kinds.

Regards,
Moiz Kothari

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


------=_Part_2017_31975457.1162274788351--