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.