Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2FC419FB257 for ; Tue, 31 Oct 2006 02:54:24 -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 70047-06 for ; Tue, 31 Oct 2006 02:54:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50802.mail.yahoo.com (web50802.mail.yahoo.com [206.190.38.111]) by postgresql.org (Postfix) with SMTP id BEE349FB311 for ; Tue, 31 Oct 2006 02:54:19 -0400 (AST) Received: (qmail 10961 invoked by uid 60001); 31 Oct 2006 06:54:18 -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=iQ47C6IP+ywKbbCJ8rsKgo5YbuNaQYEM3BCSaB8IQRJzhrYfq8Zc+Ps1yia5oX17BZCgj1IPgd3ceuZO7+0Ac/yiTzVmh0LfnJfydNjWrAZOaCeRK72/Ja7E0W4IiCJTquNEspZHdmWaX5D+V4D2AZ+g9FPm53DjihoeE+5cHps= ; Message-ID: <20061031065418.10959.qmail@web50802.mail.yahoo.com> Received: from [203.134.174.70] by web50802.mail.yahoo.com via HTTP; Mon, 30 Oct 2006 22:54:18 PST Date: Mon, 30 Oct 2006 22:54:18 -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: <610212.14371.qm@web31803.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-217612859-1162277658=:9906" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.267 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200610/245 X-Sequence-Number: 26634 --0-217612859-1162277658=:9906 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Richard, 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. Regards Roopa Richard Broersma Jr wrote: > 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? I will try, but to start with, to help us, when you have a difficult query to solve, you should simplify your query as much a possible. This way we can more quickly see what you are intending verses the problem you are having. 1 tip: (select distinct ric from ticks) I think that you will find that: (select ric from ticks group by ric) is much faster than using the distinct. The error in the query that I see is that you are using foo as a criteria in the ON syntax. This will not work. To illistrate: A,B join C ON (B.id = C.id) --ON syntax only works with joins AND (B.id2 < C.id) --The And is still part of the ON syntax --you can not reference A since it is not joined Where A.id = B.id --you can only specify a non-joined tables contrainst AND A.id2 < C.id2 ; --in the where clause I hope this helps. Regards, Richard Broersma JR. --------------------------------- We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-217612859-1162277658=:9906 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi Richard,
 
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.
 
Regards
Roopa

Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> 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?

I will try, but to start with, to help us, when you have a difficult query to solve, you should
simplify your query as much a possible. This way we can more quickly see what you are intending
verses the problem you are having.

1 tip: (select distinct ric from ticks)

I think that you will find that:
(select ric from ticks group by ric)
is much faster than using the distinct.

The error in the query that I see is that you are using foo as a criteria in the ON syntax. This
will not work. To illistrate:

A,B join C
ON (B.id = C.id) --ON syntax only works with joins
AND (B.id2 < C.id) --The And is still part of the ON syntax
--you can not reference A since it is not joined

Where
A.id = B.id --you can only specify a non-joined tables contrainst
AND
A.id2 < C.id2
; --in the where clause

I hope this helps.

Regards,

Richard Broersma JR.

We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-217612859-1162277658=:9906--