select trunc(b,'hh24') as logdate,sum(a) from ta group by trunc(b,'hh24');
SELECT t.st, SUM(ta.a) FROM ta, (SELECT lpad(LEVEL - 1, 2, '0') st, lpad(LEVEL, 2, '0') et FROM dual CONNECT BY LEVEL <= 24) t WHERE ta.b >= t.st AND ta.b < t.et GROUP BY t.st;
with ta as ( select 2 a, '09:12:00' b from dual union all select 5 a, '09:18:36' b from dual) SELECT t.st, nvl(SUM(a),0) total_a FROM ta, (SELECT lpad(LEVEL - 1, 2, '0') st, lpad(LEVEL, 2, '0') et FROM dual CONNECT BY LEVEL <= 24) t WHERE ta.b(+) >= t.st AND ta.b(+) < t.et GROUP BY t.st order by st;
select trunc(b,'hh24') as logdate,sum(a)
from ta
group by trunc(b,'hh24');
FROM ta,
(SELECT lpad(LEVEL - 1, 2, '0') st, lpad(LEVEL, 2, '0') et
FROM dual
CONNECT BY LEVEL <= 24) t
WHERE ta.b >= t.st
AND ta.b < t.et
GROUP BY t.st;
select 2 a, '09:12:00' b from dual union all
select 5 a, '09:18:36' b from dual)
SELECT t.st, nvl(SUM(a),0) total_a
FROM ta,
(SELECT lpad(LEVEL - 1, 2, '0') st, lpad(LEVEL, 2, '0') et
FROM dual
CONNECT BY LEVEL <= 24) t
WHERE ta.b(+) >= t.st
AND ta.b(+) < t.et
GROUP BY t.st
order by st;