How to group by date and not datetime in Oracle?

coredev 0
Viewed 32

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 ?

oracle18c oracle11g oracle
2 Answers

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

MT00

you should use group by time,method.

CREATE TABLE timdt 
(
    time    date,
    method  int
);

INSERT INTO timdt (time,method)VALUES('01-Sep-2022', '1');
INSERT INTO timdt (time,method)VALUES('01-Sep-2022', '2');
INSERT INTO timdt (time,method)VALUES('01-Sep-2022', '2');
INSERT INTO timdt (time,method)VALUES('01-Sep-2022', '3');
INSERT INTO timdt (time,method)VALUES('01-Sep-2022', '3');
INSERT INTO timdt (time,method)VALUES('01-Sep-2022', '3');
INSERT INTO timdt (time,method)VALUES('02-Sep-2022', '1');
INSERT INTO timdt (time,method)VALUES('03-Sep-2022', '2');
INSERT INTO timdt (time,method)VALUES('04-Sep-2022', '3');

select time,method,count(*) from timdt group by time,method order by time,method;

OUTPUT:

TIME    METHOD  COUNT(*)
01-SEP-22   1   1
01-SEP-22   2   2
01-SEP-22   3   3
02-SEP-22   1   1
03-SEP-22   2   1
04-SEP-22   3   1
Sund'er0
Related