Oracle 11 query group/count and split result by hour

Question

I have a simple query on Oracle v11 to group and count records, nothing special:

select ADDR, count(ADDR) from DBTB group by ADDR;

The rable have also the TIMESTAMP column, what I'm trying to do is group and count unique ADDR by hour. e.g. on table:

TIMESTAMP               ADDR
19-OCT-2021 17:15:00    12345
19-OCT-2021 17:20:00    12345
19-OCT-2021 17:25:00    12345
19-OCT-2021 17:27:00    67890
19-OCT-2021 18:10:00    55555
19-OCT-2021 18:20:00    55555
19-OCT-2021 18:30:00    66666
19-OCT-2021 18:43:00    77777

The output should be:

HOUR  COUNT
17        2
18        3

Could someone help me to do a query to count and group the same ADDR split by hour? Thank you Lucas

Answer 1

Use TO_CHAR() to get the hour of each timestamp:

SELECT TO_CHAR("TIMESTAMP", 'HH24') HOUR,
       COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
GROUP BY TO_CHAR("TIMESTAMP", 'HH24');

Probably you also need to group by the date:

SELECT TRUNC("TIMESTAMP") DAY,
       TO_CHAR("TIMESTAMP", 'HH24') HOUR,
       COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
GROUP BY TRUNC("TIMESTAMP"), TO_CHAR("TIMESTAMP", 'HH24');

Or filter for a specific date:

SELECT TO_CHAR("TIMESTAMP", 'HH24') HOUR,
       COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
WHERE TRUNC("TIMESTAMP") = TO_DATE('19-Oct-2021', 'DD-MON-YYYY')
GROUP BY TO_CHAR("TIMESTAMP", 'HH24');

See the demo.