How to group by date and not datetime in Oracle?

I have few columns which are time and method etc. I need to display the operations performed on a day.

time,method 
01-Sep-2022,1
01-Sep-2022,2
01-Sep-2022,2
01-Sep-2022,3
01-Sep-2022,3
01-Sep-2022,3
02-Sep-2022,1
03-Sep-2022,1
04-Sep-2022,1


Output
time,method 
01-Sep-2022,1,1
01-Sep-2022,2,2
01-Sep-2022,3,3
02-Sep-2022,1,1
03-Sep-2022,1,1
04-Sep-2022,1,1

How to write the Oracle query ?

Solution

How to group by date and not datetime in Oracle?

Oracle does not have a DATETIME data-type; it only has DATE and TIMESTAMP and both always contain a time component (even if the user interface you are using may choose to only display the date component, it still always has a time component).

To group by the date component, use the TRUNC function to truncate the time component back to midnight so that all values on the same day have the same truncated time:

SELECT TRUNC(time) AS day,
       method,
       count(*)
FROM   table_name
GROUP BY TRUNC(time), method
ORDER BY day, method;

Which, for the sample data:

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;

Outputs:

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

fiddle