Re: Add calculated fields from one table to other table

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(at)yahoo(dot)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(at)yahoo(dot)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_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.<http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index>
>
>