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>
Date: Thu, 2 Nov 2006 22:03:37 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Add calculated fields from one table to other table
To: roopa perumalraja <roopabenzer@yahoo.com>, 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

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