> 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)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
