Re: Add calculated fields from one table to other table

Hi

I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries table has fields like ticker, time, avg_price, avg_volume.

The time field in Timeseries table is different from time in tick table, its the timeseries for every minute. Now I want to calculate the average price & volume from tick table for each ticker and for every minute and add those fields to timeseries table. Can anyone please help me out with the sql query.

Note: The ticker in the tick table also has duplicate values, so i am not able to create relation between two tables.

Thanks in advance
Roopa

---------------------------------
Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.

On 10/25/06, roopa perumalraja <roopabenzer(at)yahoo(dot)com> wrote:
>
> Hi
>
> I have two tables. Tick table has fields like ticker, time, price & volume
> and Timeseries table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table,
> its the timeseries for every minute. Now I want to calculate the average
> price & volume from tick table for each ticker and for every minute and add
> those fields to timeseries table. Can anyone please help me out with the sql
> query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not
> able to create relation between two tables.
>

Will this help:

select
ticker,
date_trunc('minute', time),
ave(price),
ave(volume)
from tick
group by
ticker,
date_trunc('minute', time)

You say you want to "add" these values to the Timeseries table? You mean
insert them? If so do this:

insert into timeseries (
ticker, time, avg_price, avg_volume
)
select
ticker,
date_trunc('minute', time),
ave(price),
ave(volume)
from tick
group by
ticker,
date_trunc('minute', time)

Of course if you do this repeatedly, you will start gathering duplicates in
the timeseries so you may want to do one insert and one update:

insert into timeseries (
ticker, time, avg_price, avg_volume
)
select
tick.ticker,
date_trunc('minute', tick.time),
ave(tick.price),
ave(tick.volume)
from tick
left outer join timeseries on (
-- Not sure your join since you said time is not the same between ticke
and timeseries
date_trunc('minute', tick.time) = timeseries.tick
and tick.ticker = timeseries.ticker
)
group by
ticker,
date_trunc('minute', time)
having timeseries.ticker is null

... I will leave the update as an exercise ;)

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================

roopa perumalraja <roopabenzer(at)yahoo(dot)com> wrote: Hi

I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries table has fields like ticker, time, avg_price, avg_volume.

The time field in Timeseries table is different from time in tick table, its the timeseries for every minute. Now I want to calculate the average price & volume from tick table for each ticker and for every minute and add those fields to timeseries table. Can anyone please help me out with the sql query.

Note: The ticker in the tick table also has duplicate values, so i am not able to create relation between two tables.

Thanks in advance
Roopa

---------------------------------
You can't get there from here. It is like saying a hitter has a .250 average and in this game he was 2 for 4, what's his average? Timeseries must also have quantity and price, just as you would need to know number of bats and hits for the baseball player.

---------------------------------
Want to be your own boss? Learn how on Yahoo! Small Business.

> I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table, its the timeseries
> for every minute. Now I want to calculate the average price & volume from tick table for each
> ticker and for every minute and add those fields to timeseries table. Can anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not able to create
> relation between two tables.

Here is my guess how it can be done:

insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...

where select .... would be

select tick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;

Regards,

Richard Broersma Jr.

Hi

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.

Thanks a lot in advance
Roopa

Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
> I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table, its the timeseries
> for every minute. Now I want to calculate the average price & volume from tick table for each
> ticker and for every minute and add those fields to timeseries table. Can anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not able to create
> relation between two tables.

Here is my guess how it can be done:

insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...

where select .... would be

select tick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;

Regards,

Richard Broersma Jr.

---------------------------------
Get your email and see which of your friends are online - Right on the new Yahoo.com

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

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_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)

---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.

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

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_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_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)

---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.

---------------------------------
Get your email and see which of your friends are online - Right on the new Yahoo.com

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

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

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

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(at)yahoo(dot)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.

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

How about:

SELECT
foo.ric,
date_trunc('minute', tm.times_time) as minute,
count(tk.*),