By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (time, method) AS
SELECT DATE '2022-09-01' + INTERVAL '1' HOUR, 1 FROM DUAL UNION ALL
SELECT DATE '2022-09-01' + INTERVAL '2' HOUR, 2 FROM DUAL UNION ALL
SELECT DATE '2022-09-01' + INTERVAL '3' HOUR, 2 FROM DUAL UNION ALL
SELECT DATE '2022-09-01' + INTERVAL '4' HOUR, 3 FROM DUAL UNION ALL
SELECT DATE '2022-09-01' + INTERVAL '5' HOUR, 3 FROM DUAL UNION ALL
SELECT DATE '2022-09-01' + INTERVAL '6' HOUR, 3 FROM DUAL UNION ALL
SELECT DATE '2022-09-02' + INTERVAL '7' HOUR, 1 FROM DUAL UNION ALL
SELECT DATE '2022-09-03' + INTERVAL '8' HOUR, 1 FROM DUAL UNION ALL
SELECT DATE '2022-09-04' + INTERVAL '9' HOUR, 1 FROM DUAL;
9 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TRUNC(time) AS day,
method,
count(*)
FROM table_name
GROUP BY TRUNC(time), method
ORDER BY day, method;
| DAY | METHOD | COUNT(*) |
|---|---|---|
| 2022-09-01 00:00:00 | 1 | 1 |
| 2022-09-01 00:00:00 | 2 | 2 |
| 2022-09-01 00:00:00 | 3 | 3 |
| 2022-09-02 00:00:00 | 1 | 1 |
| 2022-09-03 00:00:00 | 1 | 1 |
| 2022-09-04 00:00:00 | 1 | 1 |