Re: Add calculated fields from one table to other table

Roopa,

Why dont you try putting in some case or decode with your first field, so
incase if nothing is returned you explicitly make it 'A' kinds.

Regards,
Moiz Kothari

On 10/31/06, roopa perumalraja <roopabenzer(at)yahoo(dot)com> wrote:
>
> Hi
>
> Thanks a lot for your help. The query which you suggested gives me a
> result like this
>
> A | 12:00| 12 | 64.99 | 63.99
> | 12:01 | 0 | |
> A | 12:02 | 5 | 36.99 | 32.99
>
> but I wanted the result to look like this
>
> A | 12:00| 12 | 64.99 | 63.99
> A | 12:01 | 0 | |
> A | 12:02 | 5 | 36.99 | 32.99
>
> Can anybody help me with that.
>
> Thanks in advance
> Roopa
>
> *Moiz Kothari <moizpostgres(at)gmail(dot)com>* wrote:
>
> 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_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
> > 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>
> >
>
>
> ------------------------------
> Get your email and see which of your friends are online - Right on the new
> Yahoo.com <http://us.rd.yahoo.com/evt=42973/*http://www.yahoo.com/preview>
>
>