From: roopa perumalraja <roopabenzer@yahoo.com>
To: pgsql-sql@postgresql.org
Cc: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Add calculated fields from one table to other table
Date: Sun, 29 Oct 2006 18:50:49 -0800 (PST)
Message-ID: <20061030025049.56769.qmail@web50803.mail.yahoo.com> (raw)
In-Reply-To: <20061026175934.78136.qmail@web31806.mail.mud.yahoo.com>
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@yahoo.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roopabenzer@yahoo.com, rabroersma@yahoo.com
Subject: Re: Add calculated fields from one table to other table
In-Reply-To: <20061030025049.56769.qmail@web50803.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox