Re: Add calculated fields from one table to other table

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