sql - calculation sum of cost for the full hour

Question

based on the value in the table: timestamp, cost I would like to get the increment sum of cost value every hour.

Table:

Timestamp	Price
2021-01-12 00:05:00	1
2021-01-12 01:01:00	1
2021-01-12 02:06:00	3
2021-01-12 03:01:00	3
2021-01-12 03:02:00	2

Answer 1

You can use group by date truncated to hour and then use analytical function as follows:

select timestamp_trunc(Timestamp , HOUR) as tst,
       sum(sum(price)) over (order by timestamp_trunc(Timestamp , HOUR)) as price
  from your_table t
group by tst
order by tst