From: roopa perumalraja <roopabenzer@yahoo.com>
To: pgsql-sql@postgresql.org
Cc: orgRichard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Add calculated fields from one table to other table
Date: Sun, 29 Oct 2006 21:34:26 -0800 (PST)
Message-ID: <20061030053426.67639.qmail@web50813.mail.yahoo.com> (raw)
In-Reply-To: <534177.87743.qm@web31809.mail.mud.yahoo.com>
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@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)
---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.
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: <20061030053426.67639.qmail@web50813.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