From roopabenzer@yahoo.com Thu Oct 26 01:07:40 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EA68F9FA6B5 for ; Wed, 25 Oct 2006 22:07:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 97478-05 for ; Wed, 25 Oct 2006 22:07:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50815.mail.yahoo.com (web50815.mail.yahoo.com [206.190.39.136]) by postgresql.org (Postfix) with SMTP id ED0919FA432 for ; Wed, 25 Oct 2006 22:07:37 -0300 (ADT) Received: (qmail 67475 invoked by uid 60001); 26 Oct 2006 01:07:36 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=LmEzckVkjth62wuVca+BHSjC8IV30NbZJVCc+B7HFIItDhVx4ti2qeuRIFSHQ6JqzGko2Fg5e8NbgxbPgGyq1ksvFPWpzxCtsoJGrJxggQ4qKLzPWSRKcbs3nkxpFd4ZXAcZV5IVcCCG1f8IaLTC4n7iwthMOTVRIeIHMbCrXA0= ; Message-ID: <20061026010736.67473.qmail@web50815.mail.yahoo.com> Received: from [203.134.174.70] by web50815.mail.yahoo.com via HTTP; Wed, 25 Oct 2006 18:07:36 PDT Date: Wed, 25 Oct 2006 18:07:36 -0700 (PDT) From: roopa perumalraja Subject: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1775136853-1161824856=:67125" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.75 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_30_40, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200610/208 X-Sequence-Number: 26597 --0-1775136853-1161824856=:67125 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1775136853-1161824856=:67125 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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. --0-1775136853-1161824856=:67125-- From postgresql@aranya.com Thu Oct 26 01:59:59 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 5D7DB9FB278 for ; Wed, 25 Oct 2006 22:59:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 51878-01 for ; Thu, 26 Oct 2006 01:59:54 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 3F1DB9FA6B5 for ; Wed, 25 Oct 2006 22:59:53 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so234366ugc for ; Wed, 25 Oct 2006 18:59:52 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=URkatvt9lYDS0ePTQcTgnOjsGapLIWhbr66YJtBEZT8OhvwO2FMLx5pR6trUBJk4AN0uQwnTWZIfcItCL1dNOhkfCaI3ZsP+UL9Dnh9VWTSJ44K3GokM734IVbD2GL2sv5yc+V2lbA5qHOBlylqZ5Nkxbj3xPry1jnpODvMhdKM= Received: by 10.78.185.16 with SMTP id i16mr1955898huf; Wed, 25 Oct 2006 18:59:51 -0700 (PDT) Received: by 10.78.136.6 with HTTP; Wed, 25 Oct 2006 18:59:51 -0700 (PDT) Message-ID: Date: Wed, 25 Oct 2006 20:59:51 -0500 From: "Aaron Bono" To: "roopa perumalraja" Subject: Re: Add calculated fields from one table to other table Cc: pgsql-sql@postgresql.org In-Reply-To: <20061026010736.67473.qmail@web50815.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22847_18528086.1161827991810" References: <20061026010736.67473.qmail@web50815.mail.yahoo.com> X-Google-Sender-Auth: 9a817a083cc210c7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/209 X-Sequence-Number: 26598 ------=_Part_22847_18528086.1161827991810 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/25/06, roopa perumalraja 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 ================================================================== ------=_Part_22847_18528086.1161827991810 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/25/06, roopa perumalraja <roopabenzer@yahoo.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
================================================================== ------=_Part_22847_18528086.1161827991810-- From chestercyoung@yahoo.com Sun Oct 29 05:53:02 2006 Received: from wm.hub.org (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 624639FB1DD for ; Sun, 29 Oct 2006 01:53:02 -0400 (AST) Received: from maia (wm.hub.org [200.46.204.128]) by wm.hub.org (Postfix) with ESMTP id 5BAE2DF9A71 for ; Sun, 29 Oct 2006 05:53:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web54305.mail.yahoo.com (web54305.mail.yahoo.com [206.190.49.115]) by postgresql.org (Postfix) with SMTP id DA7219FA2A9 for ; Thu, 26 Oct 2006 12:29:01 -0300 (ADT) Received: (qmail 21559 invoked by uid 60001); 26 Oct 2006 15:29:01 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=GOTi2BB2ZZNXUSlVg3kS6QvTeSW61ij6kM6LawzYvBCfoSvbU2JcCek+csoJk4GmzRPtNdCIEPKXZQBEHK7TDclVZK2AJes/kgULf8XKZrzPQmqFxrrtLIqpj3pdVoNGDO2eLrtmKuGXJBihznZlZfPrgdfYplmVFdlTKczianA= ; Message-ID: <20061026152900.21557.qmail@web54305.mail.yahoo.com> Received: from [68.35.2.64] by web54305.mail.yahoo.com via HTTP; Thu, 26 Oct 2006 08:29:00 PDT Date: Thu, 26 Oct 2006 08:29:00 -0700 (PDT) From: chester c young Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja Cc: sql pgsql In-Reply-To: <20061026010736.67473.qmail@web50815.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1586081451-1161876540=:18269" Content-Transfer-Encoding: 8bit X-Archive-Number: 200610/232 X-Sequence-Number: 26621 --0-1586081451-1161876540=:18269 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit roopa perumalraja 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. --0-1586081451-1161876540=:18269 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit roopa perumalraja <roopabenzer@yahoo.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. --0-1586081451-1161876540=:18269-- From rabroersma@yahoo.com Thu Oct 26 17:59:39 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2CF519FA2BA for ; Thu, 26 Oct 2006 14:59:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 76762-10 for ; Thu, 26 Oct 2006 14:59:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31806.mail.mud.yahoo.com (web31806.mail.mud.yahoo.com [68.142.207.69]) by postgresql.org (Postfix) with SMTP id CA6DD9FA2B9 for ; Thu, 26 Oct 2006 14:59:35 -0300 (ADT) Received: (qmail 78138 invoked by uid 60001); 26 Oct 2006 17:59:34 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=L65zGRczRzTvlyP3SE8qmNMifnYgUkcsUFUy+pgQrocGoheCEGclF+xCT6CjDv41z6jgjaWpkPgmu9E0SMVJhO3Jocx8Jml+6O3jEebVwFYcRNBMwhngLmgoD59IvJzNKLRARfVvJheJJ7naYXSXMvyDZvlINCk2wQhoC5p19ns= ; Message-ID: <20061026175934.78136.qmail@web31806.mail.mud.yahoo.com> X-YMail-OSG: SvnXOlUVM1mCx_E_0jR2OnUj0VlPhv24a7..7BHA_TdMeVy.G4uAhYYGcDO3TsdAuJWCqe.FYekfv.o06iHiR2CnssYTzuEwiU1VSwL9ARsEUK7VRqfEZFhC1o78r8fTR6gqddQJFoeuefI- Received: from [12.177.97.50] by web31806.mail.mud.yahoo.com via HTTP; Thu, 26 Oct 2006 10:59:34 PDT Date: Thu, 26 Oct 2006 10:59:34 -0700 (PDT) From: Richard Broersma Jr Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja , pgsql-sql@postgresql.org In-Reply-To: <20061026010736.67473.qmail@web50815.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.131 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: *** X-Archive-Number: 200610/210 X-Sequence-Number: 26599 > 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. From roopabenzer@yahoo.com Mon Oct 30 02:50:55 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 777AB9FB2E5 for ; Sun, 29 Oct 2006 22:50:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 41789-02 for ; Sun, 29 Oct 2006 22:50:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50803.mail.yahoo.com (web50803.mail.yahoo.com [206.190.38.112]) by postgresql.org (Postfix) with SMTP id 0CF3F9FB2D2 for ; Sun, 29 Oct 2006 22:50:50 -0400 (AST) Received: (qmail 56771 invoked by uid 60001); 30 Oct 2006 02:50:49 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=vdSPA1+2iJA10Q6w56aZn3Wr/VEnXTkncQDWsjny5VJBDJ14eIHDvLCF0AAhxCmQfAQ6fQcHDNXqMC7ludmQRxXropxtXxPKVk+S3yhX0YpmTkU3WDRhnMxxzmLeQFRQg+LkLRW3wh9N7O/CIVdamVz/l48I0bhdUE7cDjmGYp8= ; Message-ID: <20061030025049.56769.qmail@web50803.mail.yahoo.com> Received: from [203.134.174.70] by web50803.mail.yahoo.com via HTTP; Sun, 29 Oct 2006 18:50:49 PST Date: Sun, 29 Oct 2006 18:50:49 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org Cc: Richard Broersma Jr In-Reply-To: <20061026175934.78136.qmail@web31806.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1836043908-1162176649=:42654" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.799 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200610/234 X-Sequence-Number: 26623 --0-1836043908-1162176649=:42654 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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 --0-1836043908-1162176649=:42654 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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 --0-1836043908-1162176649=:42654-- From rabroersma@yahoo.com Mon Oct 30 03:12:55 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E55279FB3DF for ; Sun, 29 Oct 2006 23:12:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43485-08 for ; Sun, 29 Oct 2006 23:12:53 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31809.mail.mud.yahoo.com (web31809.mail.mud.yahoo.com [68.142.207.72]) by postgresql.org (Postfix) with SMTP id 23F329FB3AA for ; Sun, 29 Oct 2006 23:12:53 -0400 (AST) Received: (qmail 89092 invoked by uid 60001); 30 Oct 2006 03:12:51 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=FLulx1T6oQlV/Y9Tb2y/nN3X8amB5YVV8kRyl7I9ng8Bzeh1IPnbXm4WxOrFpyy8X6GwfZZ9/a1qx2dHV8GxVr7jU7JjjVSZM7TJ7aaqi8H1ctJJhMhv+ydX3KufnI7AcdUUaUDaU/3txJmORs8Md5QEKJsVWUY0vMRO1BPuYy8= ; X-YMail-OSG: pNC5.H8VM1nV0K6J7OxFkmvlLU20xw7UzU_i6BH6r0Kptpy1m226exUeyvO2_3URDdgxN..QOBBTSIY7Hml1_1wAnsS.n8X0nVaWs9NGD4fd5wA.WX7G618XX1jnHe.fv.sooOtc6FI- Received: from [24.54.196.198] by web31809.mail.mud.yahoo.com via HTTP; Sun, 29 Oct 2006 19:12:51 PST Date: Sun, 29 Oct 2006 19:12:51 -0800 (PST) From: Richard Broersma Jr Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja , pgsql-sql@postgresql.org Cc: Richard Broersma Jr In-Reply-To: <20061030025049.56769.qmail@web50803.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <534177.87743.qm@web31809.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.798 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: ** X-Archive-Number: 200610/235 X-Sequence-Number: 26624 > 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) From roopabenzer@yahoo.com Mon Oct 30 05:34:33 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BF9049FA4C3 for ; Mon, 30 Oct 2006 01:34:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 42854-06-2 for ; Mon, 30 Oct 2006 01:34:28 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50813.mail.yahoo.com (web50813.mail.yahoo.com [206.190.39.119]) by postgresql.org (Postfix) with SMTP id B46699FA50F for ; Mon, 30 Oct 2006 01:34:27 -0400 (AST) Received: (qmail 67641 invoked by uid 60001); 30 Oct 2006 05:34:26 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=cvFPzJKHIxwauKgguPXAtMt3in9dEitUwTeQ8MoyQbk8IO0p7JR24x2HhTTgObfoFO1rG7UyyjMM2H8sJFcrFv4CBChveQds67IQhjLJuF4WIQRz4H7laVBaUi7+ZZpTLnpYViA1jJz0PPX4/jAdEglpqYgxx/2jLZZjnFPEsRU= ; Message-ID: <20061030053426.67639.qmail@web50813.mail.yahoo.com> Received: from [203.134.174.70] by web50813.mail.yahoo.com via HTTP; Sun, 29 Oct 2006 21:34:26 PST Date: Sun, 29 Oct 2006 21:34:26 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org Cc: orgRichard Broersma Jr In-Reply-To: <534177.87743.qm@web31809.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-846898232-1162186466=:67527" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.778 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200610/236 X-Sequence-Number: 26625 --0-846898232-1162186466=:67527 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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. --0-846898232-1162186466=:67527 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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. --0-846898232-1162186466=:67527-- From moizpostgres@gmail.com Mon Oct 30 05:47:28 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A1AE99FB26B for ; Mon, 30 Oct 2006 01:47:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 56998-01 for ; Mon, 30 Oct 2006 01:47:24 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 160149FB21B for ; Mon, 30 Oct 2006 01:47:23 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so915813ugc for ; Sun, 29 Oct 2006 21:47:23 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=eu7IlxQIAxMY/2fAT7ahl2av8oWElYh5HBa1Xy0eimA13NuIcf6osU8z4/wHmCW5vKDg7JN3Jj+9KKBCiG2nWvzq2ANKWsR0XZ0Vz9MznWN9dbrN7TTkb2HnCeM42RFXbkrc3n2s7Sus/zGkMdAKwKBqb1MkykPiuUTjCxQe58c= Received: by 10.67.27.3 with SMTP id e3mr3607679ugj; Sun, 29 Oct 2006 21:47:22 -0800 (PST) Received: by 10.67.26.19 with HTTP; Sun, 29 Oct 2006 21:47:22 -0800 (PST) Message-ID: <82e1a9bd0610292147q634cc985r33dd5c613d7d1ba2@mail.gmail.com> Date: Mon, 30 Oct 2006 11:17:22 +0530 From: "Moiz Kothari" To: "roopa perumalraja" Subject: Re: Add calculated fields from one table to other table Cc: pgsql-sql@postgresql.org, "orgRichard Broersma Jr" In-Reply-To: <20061030053426.67639.qmail@web50813.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_90280_18498842.1162187242686" References: <534177.87743.qm@web31809.mail.mud.yahoo.com> <20061030053426.67639.qmail@web50813.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/237 X-Sequence-Number: 26626 ------=_Part_90280_18498842.1162187242686 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 * 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. > > ------=_Part_90280_18498842.1162187242686 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@yahoo.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@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.


------=_Part_90280_18498842.1162187242686-- From roopabenzer@yahoo.com Mon Oct 30 23:20:01 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 7A8F09FB1E2 for ; Mon, 30 Oct 2006 19:20:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 98530-05 for ; Mon, 30 Oct 2006 23:19:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50814.mail.yahoo.com (web50814.mail.yahoo.com [206.190.39.120]) by postgresql.org (Postfix) with SMTP id EC60B9FB1E6 for ; Mon, 30 Oct 2006 19:19:54 -0400 (AST) Received: (qmail 16121 invoked by uid 60001); 30 Oct 2006 23:19:53 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=jSqMJHE8pqKhtSWakKAFUmkOWMAQL7IBMqVFLMiEyyNgzBIpYRn4EOaKB+4lnNrG6i5SagWVk97JmBopguTuw+06kw82vVgYNTvSNquV/sh0J3Cbb1nGF/qjBs57ka0ygjWkGEYi8XquBl4nIQwxNOH084SfUP4RkjH2XR+oHIw= ; Message-ID: <20061030231953.16119.qmail@web50814.mail.yahoo.com> Received: from [203.134.174.70] by web50814.mail.yahoo.com via HTTP; Mon, 30 Oct 2006 15:19:53 PST Date: Mon, 30 Oct 2006 15:19:53 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org Cc: Moiz Kothari In-Reply-To: <82e1a9bd0610292147q634cc985r33dd5c613d7d1ba2@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-349515138-1162250393=:15568" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.131 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_30_40, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200610/238 X-Sequence-Number: 26627 --0-349515138-1162250393=:15568 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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@yahoo.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 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 --0-349515138-1162250393=:15568 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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@gmail.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@yahoo.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@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.


Get your email and see which of your friends are online - Right on the new Yahoo.com --0-349515138-1162250393=:15568-- From roopabenzer@yahoo.com Tue Oct 31 03:02:03 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E1EC69F9F44 for ; Mon, 30 Oct 2006 23:02:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 58905-04 for ; Tue, 31 Oct 2006 03:01:25 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50804.mail.yahoo.com (web50804.mail.yahoo.com [206.190.38.113]) by postgresql.org (Postfix) with SMTP id 7A8AE9FB21C for ; Mon, 30 Oct 2006 23:01:23 -0400 (AST) Received: (qmail 40890 invoked by uid 60001); 31 Oct 2006 03:01:20 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=w7M0mG/3AY3jb9K09GBp6csGSphKmJeQnX1imSMw3KWjETLwp4ITz9TjINCU+IsO1vz3o2WEmLZqAz/DY67MXnHNqDKG5u2SR+KcsMIiiGI1eCCAVVBddY+0uaJd2mA9puJVypywj+l2aWBtC+Ng6D3TYp2ROcgCMrM1eBG7+MQ= ; Message-ID: <20061031030120.40888.qmail@web50804.mail.yahoo.com> Received: from [203.134.174.70] by web50804.mail.yahoo.com via HTTP; Mon, 30 Oct 2006 19:01:20 PST Date: Mon, 30 Oct 2006 19:01:20 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org, Richard Broersma Jr In-Reply-To: <534177.87743.qm@web31809.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-424652521-1162263680=:40653" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.757 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200610/239 X-Sequence-Number: 26628 --0-424652521-1162263680=:40653 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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. --0-424652521-1162263680=:40653 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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@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)

We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-424652521-1162263680=:40653-- From rabroersma@yahoo.com Tue Oct 31 03:32:50 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6B3949FB21C for ; Mon, 30 Oct 2006 23:32:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 02949-08 for ; Mon, 30 Oct 2006 23:32:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31803.mail.mud.yahoo.com (web31803.mail.mud.yahoo.com [68.142.207.66]) by postgresql.org (Postfix) with SMTP id 5C6199F976E for ; Mon, 30 Oct 2006 23:32:47 -0400 (AST) Received: (qmail 14840 invoked by uid 60001); 31 Oct 2006 03:32:45 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=GruWMCVlMnh0wIPTjtEG0/fqAQvAbkZxBPOD8EQzFIG4TQuQNT8JDPKl4BvVBNBAmW7Np+I5qhLw58SPuu7MddLvPzC25lUOkoezAQ/2CPSLiRiYZEsYn9tOtLXUXWvL9aJ8GqqYjVwzT2OwzKc2abyh8+JOlz5vCeKgoTWP9HU= ; X-YMail-OSG: auvPT2IVM1lAXc7jBkg7JD4YNvlRNJDSQNX9KTBYQT3tGptzh5RdNATP29RcDJ9_E4OWqinpoTIBMa0zfKqg77Tz_7S_DD9tVajXZZzmMqG.5Lj2kJl9Dimu3953t79MkqkmTFcZIZjctaM- Received: from [24.54.196.198] by web31803.mail.mud.yahoo.com via HTTP; Mon, 30 Oct 2006 19:32:45 PST Date: Mon, 30 Oct 2006 19:32:45 -0800 (PST) From: Richard Broersma Jr Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja , pgsql-sql@postgresql.org In-Reply-To: <20061031030120.40888.qmail@web50804.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <610212.14371.qm@web31803.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.267 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: * X-Archive-Number: 200610/242 X-Sequence-Number: 26631 > 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. From moizpostgres@gmail.com Tue Oct 31 06:06:34 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id A8EED9FB311 for ; Tue, 31 Oct 2006 02:06:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 16733-07 for ; Tue, 31 Oct 2006 06:06:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 0327C9FB257 for ; Tue, 31 Oct 2006 02:06:29 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so1191468ugc for ; Mon, 30 Oct 2006 22:06:28 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=UoANva3It1xoX1Si0LSCUetVYhJog6XAYtJVMZ8U2igRS5r+srk9TzejCbTLmwLOqQUrk6sxDjNa/9xqTiw8wJLdlWh5nVhv9eTEtwKnNM+G+6xXyBCU0xfAn8Yte7Ow1pCB9A7AaEsSO+UlfjVoAsdFmH5IXov7/GuRVQ+sPis= Received: by 10.66.244.10 with SMTP id r10mr5550778ugh; Mon, 30 Oct 2006 22:06:28 -0800 (PST) Received: by 10.67.26.19 with HTTP; Mon, 30 Oct 2006 22:06:28 -0800 (PST) Message-ID: <82e1a9bd0610302206j2b2f5725pc0b66204127fcff3@mail.gmail.com> Date: Tue, 31 Oct 2006 11:36:28 +0530 From: "Moiz Kothari" To: "roopa perumalraja" Subject: Re: Add calculated fields from one table to other table Cc: pgsql-sql@postgresql.org In-Reply-To: <20061030231953.16119.qmail@web50814.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2017_31975457.1162274788351" References: <82e1a9bd0610292147q634cc985r33dd5c613d7d1ba2@mail.gmail.com> <20061030231953.16119.qmail@web50814.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/243 X-Sequence-Number: 26632 ------=_Part_2017_31975457.1162274788351 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 * 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@yahoo.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 * 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. > > > > > > > ------------------------------ > Get your email and see which of your friends are online - Right on the new > Yahoo.com > > ------=_Part_2017_31975457.1162274788351 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@yahoo.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@gmail.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@yahoo.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@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.


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


------=_Part_2017_31975457.1162274788351-- From roopabenzer@yahoo.com Tue Oct 31 06:54:24 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2FC419FB257 for ; Tue, 31 Oct 2006 02:54:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 70047-06 for ; Tue, 31 Oct 2006 02:54:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50802.mail.yahoo.com (web50802.mail.yahoo.com [206.190.38.111]) by postgresql.org (Postfix) with SMTP id BEE349FB311 for ; Tue, 31 Oct 2006 02:54:19 -0400 (AST) Received: (qmail 10961 invoked by uid 60001); 31 Oct 2006 06:54:18 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=iQ47C6IP+ywKbbCJ8rsKgo5YbuNaQYEM3BCSaB8IQRJzhrYfq8Zc+Ps1yia5oX17BZCgj1IPgd3ceuZO7+0Ac/yiTzVmh0LfnJfydNjWrAZOaCeRK72/Ja7E0W4IiCJTquNEspZHdmWaX5D+V4D2AZ+g9FPm53DjihoeE+5cHps= ; Message-ID: <20061031065418.10959.qmail@web50802.mail.yahoo.com> Received: from [203.134.174.70] by web50802.mail.yahoo.com via HTTP; Mon, 30 Oct 2006 22:54:18 PST Date: Mon, 30 Oct 2006 22:54:18 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org, Richard Broersma Jr In-Reply-To: <610212.14371.qm@web31803.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-217612859-1162277658=:9906" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.267 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200610/245 X-Sequence-Number: 26634 --0-217612859-1162277658=:9906 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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. --0-217612859-1162277658=:9906 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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@yahoo.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. --0-217612859-1162277658=:9906-- From rabroersma@yahoo.com Tue Oct 31 13:27:38 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9FF449FAE4E for ; Tue, 31 Oct 2006 09:27:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75067-05 for ; Tue, 31 Oct 2006 09:27:34 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31803.mail.mud.yahoo.com (web31803.mail.mud.yahoo.com [68.142.207.66]) by postgresql.org (Postfix) with SMTP id EA85A9FA5B5 for ; Tue, 31 Oct 2006 09:27:33 -0400 (AST) Received: (qmail 30289 invoked by uid 60001); 31 Oct 2006 13:27:32 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=m4TUk1rjeYNNpnhbcdqcc8HrI/2rkvEqzhpJXOFniXqA8vKB7zKWC6ojHIt1c7iQdVFDH10HMllipiUDJdxreRCatN0S84E0FA9wpzCPojXaKP24w27cIU8s2/dTEqUekK5jfNc+0I27eKVarB0Ljsrs9p0QG7p+bHmRbnRaOA4= ; X-YMail-OSG: PWLBEisVM1lHtZwYKL8hGq4F8WfvGmK8Xx31l4dhLdWNhMHj.WgESzq4qvNZNnQ49JpQ97oTckqczTazVeuCYxomsGT6xG8deetbQr6ncLDGIAg9BQi6wLwPJKucdl19dXMLS20VSAsalqY- Received: from [24.54.196.198] by web31803.mail.mud.yahoo.com via HTTP; Tue, 31 Oct 2006 05:27:32 PST Date: Tue, 31 Oct 2006 05:27:32 -0800 (PST) From: Richard Broersma Jr Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja , pgsql-sql@postgresql.org In-Reply-To: <20061031065418.10959.qmail@web50802.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <554162.29041.qm@web31803.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.886 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200610/246 X-Sequence-Number: 26635 > 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.*), From roopabenzer@yahoo.com Wed Nov 1 03:54:45 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 445E59FA12B for ; Tue, 31 Oct 2006 23:54:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 13867-07 for ; Wed, 1 Nov 2006 03:54:42 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50809.mail.yahoo.com (web50809.mail.yahoo.com [206.190.38.252]) by postgresql.org (Postfix) with SMTP id B720B9F9F80 for ; Tue, 31 Oct 2006 23:54:41 -0400 (AST) Received: (qmail 76571 invoked by uid 60001); 1 Nov 2006 03:54:40 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=DEDtfVZkG6EKeRYyIwabky5si2wzauJulb3fBRzlMBHFLP5INTAuoXGEN91ugy4i0l9YIjUMLtnL+EV+udIUM0e54/fOxAfpxdRiFm3c/uiZqiJiYYTmb+lXECaZcFp7SCbnttIdOl6e0OWF5VES+rREC26LjxARd/68YXuMf2g= ; Message-ID: <20061101035440.76569.qmail@web50809.mail.yahoo.com> Received: from [203.134.174.70] by web50809.mail.yahoo.com via HTTP; Tue, 31 Oct 2006 19:54:40 PST Date: Tue, 31 Oct 2006 19:54:40 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org, Richard Broersma Jr In-Reply-To: <404822.35369.qm@web31809.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-863165699-1162353280=:76534" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.757 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200610/263 X-Sequence-Number: 26652 --0-863165699-1162353280=:76534 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Richard, Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I am able to get the result for a particular ric. Can you help me with getting the result for all the rics in the ticks table Thanks Roopa Richard Broersma Jr wrote: > 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. Sorry I prematurely sent my first email Select foo.ric, date_trunc("minute", tm.times_time) as time_tick, count(tk.*) tickperminute, avg(tk.price), ... --your other aggregate functions from ( select ric from ticks where ric = 'A' group by ric ) as foo join ticks tk on (tk.ric = foo.ric) right join times tm on (tk.tick_time >= tm.times_time) and (tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and (tk.ric = 'A') -- this shouldn't be neccessary -- if you restructor your join -- since foo limits all ric to 'A' -- but since it is on the wrong side -- of an outer join it can't. group by foo.ric, time_tick order by time_tick; Regards, Richard Broersma Jr. --------------------------------- Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates. --0-863165699-1162353280=:76534 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi Richard,
 
Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I am able to get the result for a particular ric.
 
Can you help me with getting the result for all the rics in the ticks table
 
Thanks
Roopa

Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> 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.

Sorry I prematurely sent my first email

Select foo.ric,
date_trunc("minute", tm.times_time) as time_tick,
count(tk.*) tickperminute,
avg(tk.price),
... --your other aggregate functions
from (
select ric
from ticks
where ric = 'A'
group by ric
) as foo
join ticks tk
on (tk.ric = foo.ric)
right join times tm
on (tk.tick_time >= tm.times_time)
and (tk.tick_time < (tm.times_time + '1 minute' :: interval)::time
and (tk.ric = 'A') -- this shouldn't be neccessary
-- if you restructor your join
-- since foo limits all ric to 'A'
-- but since it is on the wrong side
-- of an outer join it can't.
group by foo.ric,
time_tick

order by time_tick;

Regards,

Richard Broersma Jr.

Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates. --0-863165699-1162353280=:76534-- From rabroersma@yahoo.com Wed Nov 1 05:51:57 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 913809FB2F8 for ; Wed, 1 Nov 2006 01:51:57 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 41278-05 for ; Wed, 1 Nov 2006 05:51:16 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31808.mail.mud.yahoo.com (web31808.mail.mud.yahoo.com [68.142.207.71]) by postgresql.org (Postfix) with SMTP id C86C39FB2E1 for ; Wed, 1 Nov 2006 01:51:13 -0400 (AST) Received: (qmail 51130 invoked by uid 60001); 1 Nov 2006 05:51:13 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=cBZnU4786uPCvEVtSA3vytssS+oPVHwu5KjrsL5e47LMISPU+653j71OUJlxv/qFoO8LyaFgLEeznKOzRKRbMyvM0ndjOpgqDv6jJXuZb50LO40nNgmJwqOeaHw8jtJCfMNGNE1mdflWGERmjMEqDSmr3/lkPx87aXv8JiPijbQ= ; X-YMail-OSG: mjMdZC8VM1n4ZCfMW31l_dp.RgMHu4pLlWqbav8arnE1C43s3wCi33Q5Xcb4aE3xQwcF9T8yxf4SmIyK8gAiuCDlIACH2N6z96pDkOFxYtBJ6aKflPf4Hew3SPe1W4LrSUaJgGxh0Q7jqqxM9rzkmfS7RuJfiAf44RJN1BoLm2BUIBx3Nm8nqiO42hd_ Received: from [24.54.196.198] by web31808.mail.mud.yahoo.com via HTTP; Tue, 31 Oct 2006 21:51:12 PST Date: Tue, 31 Oct 2006 21:51:12 -0800 (PST) From: Richard Broersma Jr Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja , pgsql-sql@postgresql.org In-Reply-To: <20061101035440.76569.qmail@web50809.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <50323.50500.qm@web31808.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.756 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200611/1 X-Sequence-Number: 26653 > Hi Richard, > > Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I > am able to get the result for a particular ric. > > Can you help me with getting the result for all the rics in the ticks table > > Thanks > Roopa Could you send create table statements for the tables you are working on, and a few insert statements for each table to have sample data. then show what you want the query results to look like. But from what you stated in your previous emails here is what I gather: maybe it might work? select tk.ric as ric, tm.times_time as minute, --timestamps by minutes count(tk.*) as , ... from times tm left join ticks tk on (tm.times_time = date_trunc('minutes', tk.time)) group by ric, minute order by minute; From roopabenzer@yahoo.com Wed Nov 1 06:49:39 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 28FEC9FB282 for ; Wed, 1 Nov 2006 02:49:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 54188-03 for ; Wed, 1 Nov 2006 06:49:16 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50812.mail.yahoo.com (web50812.mail.yahoo.com [206.190.39.107]) by postgresql.org (Postfix) with SMTP id 9E1FB9FA085 for ; Wed, 1 Nov 2006 02:49:16 -0400 (AST) Received: (qmail 80876 invoked by uid 60001); 1 Nov 2006 06:49:15 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=z2PacsVstxOf3qD3axT644V7C2/N/PAkH3XQVs69JuCFjjux/aLOuDq08dv5O7kzB47+CUgobW0eh1NP+0JGZGFBKKyUgYYyAJ2WaaWL943BG2SODqXw8k1bAry66kGPTM3FpWtdIybm72tDyWHdwfZAcQpSaUri1wJqE+N0ZF4= ; Message-ID: <20061101064915.80874.qmail@web50812.mail.yahoo.com> Received: from [203.134.174.70] by web50812.mail.yahoo.com via HTTP; Tue, 31 Oct 2006 22:49:15 PST Date: Tue, 31 Oct 2006 22:49:15 -0800 (PST) From: roopa perumalraja Subject: Re: Add calculated fields from one table to other table To: pgsql-sql@postgresql.org, Richard Broersma Jr In-Reply-To: <50323.50500.qm@web31808.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1065713914-1162363755=:80282" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.253 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_40_50, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200611/3 X-Sequence-Number: 26655 --0-1065713914-1162363755=:80282 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Richard, Thanks a lot. I am sending you the create statement of tables & few insert statements as well. Hope this helps to solve the problem. CREATE TABLE ticks ( tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass), ric varchar(30) NOT NULL, tick_date date NOT NULL, tick_time time NOT NULL, price float8, volume int4, CONSTRAINT ticks_pkey PRIMARY KEY (tick_id), ) WITHOUT OIDS; CREATE TABLE times ( times_time time NOT NULL, count int4, CONSTRAINT times_pkey PRIMARY KEY (times_time) ) selct statement of ticks table ric | tick_date | tick_time | price -----+------------+--------------+------- A | 2006-04-04 | 00:00:55.023 | 4.05 AA | 2006-04-04 | 00:00:55.023 | 9.05 A | 2006-04-04 | 00:00:59.023 | 6.05 A | 2006-04-04 | 00:01:00.023 | 5.05 ABC | 2006-04-04 | 00:01:00.509 |12.00 ABI | 2006-04-04 | 00:01:03.511 |13.00 AA | 2006-04-04 | 00:01:08.023 | 6.05 ABT | 2006-04-04 | 00:01:08.518 | 3.06 ABT | 2006-04-04 | 00:01:09.518 | 7.06 select statement of times table times_time ----------- 00:00:00 00:01:00 00:02:00 I want the query result to look ric | times_time | count | avg_price ----+------------+-------+----------- A | 00:00:00 | 2 | 5.05 AA | 00:00:00 | 1 | 9.05 ABC | 00:00:00 | 0 | ABI | 00:00:00 | 0 | ABT | 00:00:00 | 0 | A | 00:01:00 | 1 | 5.05 AA | 00:01:00 | 1 | 6.05 ABC | 00:01:00 | 1 |12.00 ABI | 00:01:00 | 1 |13.00 ABT | 00:01:00 | 2 | 5.06 I am really thankful to you. Regards Roopa Richard Broersma Jr wrote: > Hi Richard, > > Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I > am able to get the result for a particular ric. > > Can you help me with getting the result for all the rics in the ticks table > > Thanks > Roopa Could you send create table statements for the tables you are working on, and a few insert statements for each table to have sample data. then show what you want the query results to look like. But from what you stated in your previous emails here is what I gather: maybe it might work? select tk.ric as ric, tm.times_time as minute, --timestamps by minutes count(tk.*) as , ... from times tm left join ticks tk on (tm.times_time = date_trunc('minutes', tk.time)) group by ric, minute order by minute; --------------------------------- We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-1065713914-1162363755=:80282 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi Richard,
 
Thanks a lot.
 
I am sending you the create statement of tables & few insert statements as well. Hope this helps to solve the problem.
 
CREATE TABLE ticks
(
  tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass),
  ric varchar(30) NOT NULL,
  tick_date date NOT NULL,
  tick_time time NOT NULL,
  price float8,
  volume int4,
  CONSTRAINT ticks_pkey PRIMARY KEY (tick_id),
)
WITHOUT OIDS;
 
CREATE TABLE times
(
  times_time time NOT NULL,
  count int4,
  CONSTRAINT times_pkey PRIMARY KEY (times_time)
)
 
selct statement of ticks table
 ric | tick_date  |  tick_time   | price
-----+------------+--------------+-------
 A   | 2006-04-04 | 00:00:55.023 | 4.05  
 AA  | 2006-04-04 | 00:00:55.023 | 9.05
 A   | 2006-04-04 | 00:00:59.023 | 6.05   
 A   | 2006-04-04 | 00:01:00.023 | 5.05
 ABC | 2006-04-04 | 00:01:00.509 |12.00     
 ABI | 2006-04-04 | 00:01:03.511 |13.00     
 AA  | 2006-04-04 | 00:01:08.023 | 6.05     
 ABT | 2006-04-04 | 00:01:08.518 | 3.06
 ABT | 2006-04-04 | 00:01:09.518 | 7.06    
 
select statement of times table
times_time
-----------
 00:00:00       
 00:01:00       
 00:02:00       
       

I want the query result to look
ric | times_time | count | avg_price
----+------------+-------+-----------
 A  | 00:00:00   | 2  | 5.05
 AA | 00:00:00   | 1     | 9.05
ABC | 00:00:00   | 0     |
ABI | 00:00:00   | 0     |
ABT | 00:00:00   | 0     |
 A  | 00:01:00   | 1  | 5.05
 AA | 00:01:00   | 1     | 6.05
ABC | 00:01:00   | 1     |12.00
ABI | 00:01:00   | 1     |13.00
ABT | 00:01:00   | 2     | 5.06
 
I am really thankful to you.
 
Regards
Roopa


Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> Hi Richard,
>
> Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I
> am able to get the result for a particular ric.
>
> Can you help me with getting the result for all the rics in the ticks table
>
> Thanks
> Roopa

Could you send create table statements for the tables you are working on, and a few insert
statements for each table to have sample data. then show what you want the query results to look
like.

But from what you stated in your previous emails here is what I gather: maybe it might work?

select tk.ric as ric,
tm.times_time as minute, --timestamps by minutes
count(tk.*) as ,
...
from times tm
left join ticks tk
on (tm.times_time = date_trunc('minutes', tk.time))
group by ric, minute
order by minute;





We have the perfect Group for you. Check out the handy changes to Yahoo! Groups. --0-1065713914-1162363755=:80282-- From rabroersma@yahoo.com Fri Nov 3 06:03:43 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B9D7D9FA1FD for ; Fri, 3 Nov 2006 02:03:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 17408-05 for ; Fri, 3 Nov 2006 06:03:41 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31805.mail.mud.yahoo.com (web31805.mail.mud.yahoo.com [68.142.207.68]) by postgresql.org (Postfix) with SMTP id B7B0C9FA1A7 for ; Fri, 3 Nov 2006 02:03:38 -0400 (AST) Received: (qmail 38645 invoked by uid 60001); 3 Nov 2006 06:03:37 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=erf3im87WV1U3fFHK+ulZsgqCT0oHGQoHxGUZq6YYdbDPvN9GJOMSaESiaOl79+Ky3hexZcxXBUbXoQq9QPxsYsdfCONAgmBB+PDmM7qIKjZ6e4qhM3lwqxZ48UOwwlPgKhF8MvMlq+COx95VgUBUgblE6D07NB19qRVk9Ywt5w= ; Message-ID: <20061103060337.38643.qmail@web31805.mail.mud.yahoo.com> X-YMail-OSG: q_QgeDsVM1m3wxOsFZVQGjKIt8XYbP2BzYaCnuUu5leq2MhWaoAipGf2daW.kwwLMA-- Received: from [24.54.196.198] by web31805.mail.mud.yahoo.com via HTTP; Thu, 02 Nov 2006 22:03:37 PST Date: Thu, 2 Nov 2006 22:03:37 -0800 (PST) From: Richard Broersma Jr Subject: Re: Add calculated fields from one table to other table To: roopa perumalraja , pgsql-sql@postgresql.org In-Reply-To: <20061101064915.80874.qmail@web50812.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.356 tagged_above=0 required=5 tests=BAYES_50, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: *** X-Archive-Number: 200611/31 X-Sequence-Number: 26683 > I am sending you the create statement of tables & few insert statements as well. Hope this > helps to solve the problem. where are the insert statements? ;) > > CREATE TABLE ticks > ( > tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass), > ric varchar(30) NOT NULL, > tick_date date NOT NULL, > tick_time time NOT NULL, > price float8, > volume int4, > CONSTRAINT ticks_pkey PRIMARY KEY (tick_id), > ) > WITHOUT OIDS; > > CREATE TABLE times > ( > times_time time NOT NULL, > count int4, > CONSTRAINT times_pkey PRIMARY KEY (times_time) > ) > > selct statement of ticks table > ric | tick_date | tick_time | price > -----+------------+--------------+------- > A | 2006-04-04 | 00:00:55.023 | 4.05 > AA | 2006-04-04 | 00:00:55.023 | 9.05 > A | 2006-04-04 | 00:00:59.023 | 6.05 > A | 2006-04-04 | 00:01:00.023 | 5.05 > ABC | 2006-04-04 | 00:01:00.509 |12.00 > ABI | 2006-04-04 | 00:01:03.511 |13.00 > AA | 2006-04-04 | 00:01:08.023 | 6.05 > ABT | 2006-04-04 | 00:01:08.518 | 3.06 > ABT | 2006-04-04 | 00:01:09.518 | 7.06 > > select statement of times table > times_time > ----------- > 00:00:00 > 00:01:00 > 00:02:00 > > > I want the query result to look > ric | times_time | count | avg_price > ----+------------+-------+----------- > A | 00:00:00 | 2 | 5.05 > AA | 00:00:00 | 1 | 9.05 > ABC | 00:00:00 | 0 | > ABI | 00:00:00 | 0 | > ABT | 00:00:00 | 0 | > A | 00:01:00 | 1 | 5.05 > AA | 00:01:00 | 1 | 6.05 > ABC | 00:01:00 | 1 |12.00 > ABI | 00:01:00 | 1 |13.00 > ABT | 00:01:00 | 2 | 5.06 Here is what I got: ric | minute | count | avg_price -----+----------+-------+------------------ ABC | 00:00:00 | 0 | 0 ABT | 00:00:00 | 0 | 0 AA | 00:00:00 | 2 | 9.05 ABI | 00:00:00 | 0 | 0 A | 00:00:00 | 6 | 5.05 A | 00:01:00 | 3 | 5.05 ABI | 00:01:00 | 1 | 13 AA | 00:01:00 | 2 | 6.05 ABT | 00:01:00 | 9 | 5.72666666666667 ABC | 00:01:00 | 1 | 12 A | 00:02:00 | 0 | 0 AA | 00:02:00 | 0 | 0 ABI | 00:02:00 | 0 | 0 ABC | 00:02:00 | 0 | 0 ABT | 00:02:00 | 0 | 0 (15 rows) And here is how I got it: SELECT A.ric, A.minute, count(B.*) as count, COALESCE(avg(B.price),0) as avg_price FROM ( SELECT T.ric, M.times_time as minute FROM ticks T CROSS JOIN times M WHERE M.times_time BETWEEN '00:00:00' AND '00:03:00' ) A LEFT JOIN ticks B ON A.ric = B.ric AND A.minute = date_trunc('minute', B.tick_time) GROUP BY A.ric, A.minute ORDER BY A.minute ; Hope this is what you were looking for. This is the first time I've ever had to employ a cross join get what I wanted. Just realize that this query will explode with a very large number to records returned as the times table grows. You should expect a quantity of results like (total ticks * total times) Regards, Richard Broersma Jr.