DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=GruWMCVlMnh0wIPTjtEG0/fqAQvAbkZxBPOD8EQzFIG4TQuQNT8JDPKl4BvVBNBAmW7Np+I5qhLw58SPuu7MddLvPzC25lUOkoezAQ/2CPSLiRiYZEsYn9tOtLXUXWvL9aJ8GqqYjVwzT2OwzKc2abyh8+JOlz5vCeKgoTWP9HU=
	;
Date: Mon, 30 Oct 2006 19:32:45 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Add calculated fields from one table to other table
To: roopa perumalraja <roopabenzer@yahoo.com>, pgsql-sql@postgresql.org
In-Reply-To: <20061031030120.40888.qmail@web50804.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <610212.14371.qm@web31803.mail.mud.yahoo.com>

>   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.