We all know how to sum up by year
select to_char(trunc(hiredate,'Y'),'YYYY') year, count(*) from scott.emp group by trunc(hiredate,'Y') order by trunc(hiredate,'Y') YEAR COUNT(*) ---- -------- 1980 1 1981 10 1982 1 1987 2
This is quite easy. Same for day (DD), century (CC), quartal (Q), hour (HH24), month (MM)
But how do you group by, let’s say, half-year? or fiscal year, half day, 8-hours period, etc… ?
One way is to convert to number…
select
replace(
replace(
to_char(
trunc(
months_between(
hiredate,
date '1900-01-01'
)/6
)/2,
'00.0'
),
'.0','H1'
),'.5','H2') HY,
COUNT(*)
from
scott.emp
group by
trunc(months_between(hiredate, date '1900-01-01')/6)
order by
trunc(months_between(hiredate, date '1900-01-01')/6);
HY COUNT(*)
------- -----------
80H2 1
81H1 5
81H2 5
82H1 1
87H1 2
We could also add some offset to start in March and choose 180 days instead of 6 months etc… But it gets really hard to read (and write)
In Oracle 23.7, I could not test it yet, simply write …
select time_bucket(hiredate, interval '6' month, date '1900-01-01'), count(*) from scott.emp group by time_bucket(hiredate, interval '6' month, date '1900-01-01')
It is much nicer 🙂