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
Luis da silva
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
Piotr Rogowski
source
to share
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
JohnHC
source
to share