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