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 ?
CodePudding user response:
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 |
CodePudding user response:
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