Clever Geek Handbook

Group cost by hour

I have data like this

dt                 | price
---------------------------
2016-02-02 12:33:33  3.3
2016-03-02 12:33:33  3.3
2016-02-02 12:03:33  3.3
2016-02-02 12:50:33  3.3
2016-02-02 13:33:33  3.3

I want the total price grouped by hour, although I also want the year, month, and day components.

desired result:

2016-02-02 12:00:00  9.9
2016-02-02 13:00:00  3.3
2016-03-02 12:00:00  3.3

I have a query that gives this result. the question is, maybe there is a more efficient way to do this?

My current request:

SELECT TO_TIMESTAMP(d, 'YYYY-MM-DD HH24:MI:SS') AS hours, price FROM (
    SELECT substr(dt, 1, 13) AS d, sum(price) as price FROM table
    GROUP BY substr(dt, 1, 13)
) 
ORDER BY hours
+3


source to share


2 answers


try it



select
TRUNC(dt,'HH24'),
sum(price)
from table
group by TRUNC(dt,'HH24')
order by 2 desc;
+3


source


Use to_char:



select to_char(dt, 'YYYY-MM-DD HH24')||':00:00' as HourBlock, sum(price) as TotalPrice
from MyTable
group by to_char(dt, 'YYYY-MM-DD HH24')||':00:00'
+1


source






More articles:


All Articles