> Thanks for your help. That does make sense, but I am not able to get the result what I wanted
> exactly. Let me explain you.
>
> I have ticks table in which I have columns like ric, tick_time, price & volume. The times
> table has just one column with times_time which has time data for each minute ie.)
>
> Ticks
> ric | tick_time | price | volume
> A | 12:00:01 | 23.00 | 12
> A | 12:00:02 | 26.00 | 7
> B | 12: 00:02 | 8.00 | 2
> B | 12:01:01 | 45.00 | 6
>
> Times
> times_time
> 12:00
> 12:01
> 12:02
>
> Now I want the timeseries for each minute for all ric in the tick table. So my query goes like
> this for a particular ric say for example ric 'A'
>
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'A' group by
> tm.times_time, foo.ric order by tm.times_time;
>
> I get the result as I expect, but i am not able to derive a query for all rics in the tick
> table.
>
How about:
SELECT foo.ric, date_trunc('minute', tm.times_time) as minute, count(tk.*),