Re: Add calculated fields from one table to other table - Mailing list pgsql-sql

>   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 |                0ABT | 00:00:00 |     0 |
   0AA  | 00:00:00 |     2 |             9.05ABI | 00:00:00 |     0 |                0A   | 00:00:00 |     6 |
  5.05A   | 00:01:00 |     3 |             5.05ABI | 00:01:00 |     1 |               13AA  | 00:01:00 |     2 |
    6.05ABT | 00:01:00 |     9 | 5.72666666666667ABC | 00:01:00 |     1 |               12A   | 00:02:00 |     0 |
         0AA  | 00:02:00 |     0 |                0ABI | 00:02:00 |     0 |                0ABC | 00:02:00 |     0 |
           0ABT | 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.