Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E1EC69F9F44 for ; Mon, 30 Oct 2006 23:02:03 -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 58905-04 for ; Tue, 31 Oct 2006 03:01:25 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50804.mail.yahoo.com (web50804.mail.yahoo.com [206.190.38.113]) by postgresql.org (Postfix) with SMTP id 7A8AE9FB21C for ; Mon, 30 Oct 2006 23:01:23 -0400 (AST) Received: (qmail 40890 invoked by uid 60001); 31 Oct 2006 03:01:20 -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=w7M0mG/3AY3jb9K09GBp6csGSphKmJeQnX1imSMw3KWjETLwp4ITz9TjINCU+IsO1vz3o2WEmLZqAz/DY67MXnHNqDKG5u2SR+KcsMIiiGI1eCCAVVBddY+0uaJd2mA9puJVypywj+l2aWBtC+Ng6D3TYp2ROcgCMrM1eBG7+MQ= ; Message-ID: <20061031030120.40888.qmail@web50804.mail.yahoo.com> Received: from [203.134.174.70] by web50804.mail.yahoo.com via HTTP; Mon, 30 Oct 2006 19:01:20 PST Date: Mon, 30 Oct 2006 19:01:20 -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: <534177.87743.qm@web31809.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-424652521-1162263680=:40653" 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/239 X-Sequence-Number: 26628 --0-424652521-1162263680=:40653 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Thanks a lot for your help. The query which you suggested goes like this select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks_20060404 where ric = 'TRB') as foo, times tm left join ticks_20060404 tk on tk.tick_time >= tm.times_time and tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'TRB' group by tm.times_time, foo.ric order by tm.times_time; which gives me a result like this TRB | 12:00| 12 | 64.99 | 63.99 TRB | 12:01 | 0 | | TRB | 12:02 | 5 | 36.99 | 32.99 but I wanted the result for all the ric to be displayed. If I write the query like this select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks) 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 = foo.ric group by tm.times_time, foo.ric order by tm.times_time; I get a error message like this: ERROR: invalid reference to FROM-clause entry for table "foo" HINT: There is an entry for table "foo", but it cannot be referenced from this part of the query. Can you help me with this? Thanks in advance Roopa 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) --------------------------------- We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-424652521-1162263680=:40653 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi
 
Thanks a lot for your help. The query which you suggested goes like this
 
select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks_20060404 where ric = 'TRB') as foo, times tm left join ticks_20060404 tk on tk.tick_time >= tm.times_time and tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'TRB' group by tm.times_time, foo.ric order by tm.times_time;
 
which gives me a result like this
 
TRB  | 12:00| 12 | 64.99 | 63.99
TRB  | 12:01 | 0 | |
TRB | 12:02 | 5 | 36.99 | 32.99
 
but I wanted the result for all the ric to be displayed. If I write the query like this
 
select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks) 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 = foo.ric group by tm.times_time, foo.ric order by tm.times_time;
 
I get a error message like this:
 
ERROR:  invalid reference to FROM-clause entry for table "foo"
HINT:  There is an entry for table "foo", but it cannot be referenced from this part of the query.
 
Can you help me with this?
 
Thanks in advance
Roopa

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)

We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-424652521-1162263680=:40653--