ORACLE SQL: Hourly Date to be group by day time and sum of the amount

user_8980786 0
Viewed 48

I have the following situation:

ID                DATE_TIME             AMOUNT
23     14-MAY-2021 10:47:01               5
23     14-MAY-2021 11:49:52               3
23     14-MAY-2021 12:03:18               4

How can get the sum of the amount and take the DATE by day not hourly?

Example:

ID                DATE_TIME           TOTAL
23                20210514             12

I tried this way but i got error:

SELECT DISTINCT ID, TO_CHAR(DATE_TIME, 'YYYYMMDD'), SUM(AMOUNT) AS TOTAL FROM MY_TABLE
WHERE ID ='23' AND DATE_TIME > SYSDATE-1
GROUP BY TOTAL, DATE_TIME
date oracle sql
2 Answers

You don't need DISTINCT if you use GROUP BY - anything that is grouped must be distinct unless it joined to something else later on that caused it to repeat again

You were almost there too

SELECT ID, TO_CHAR(DATE_TIME, 'YYYYMMDD') AS DATE_TIME, SUM(AMOUNT) AS TOTAL 
FROM MY_TABLE
WHERE ID ='23' AND DATE_TIME > SYSDATE-1
GROUP BY ID, TO_CHAR(DATE_TIME, 'YYYYMMDD')

You need to group by the output of the function, not the input. Not every database can GROUP BY aliases used in the select (technically the SELECT hasn't been done by the time the GROUP is done so the aliases don't exist yet, and you wouldnt group by the total because that's an aggregate (the result of summing up every various value in the group)

If you need to do further work with that date, don't convert it to a string.. Cut the time off using TRUNC:

SELECT ID, TRUNC(DATE_TIME) as DATE_TIME, SUM(AMOUNT) AS TOTAL 
FROM MY_TABLE
WHERE ID ='23' AND DATE_TIME > SYSDATE-1
GROUP BY ID, TRUNC(DATE_TIME)

TRUNC can cut a date down to other parts, for example TRUNC(DATE_TIME, 'HH24') will remove the minutes and seconds but leave the hours

user_14106640

Convert the DATE column to a string with the required accuracy and then group on that:

SELECT ID,
       TO_CHAR("DATE", 'YYYY-MM-DD'),
       SUM(AMOUNT) AS TOTAL FROM MY_TABLE
WHERE  ID ='23'
AND    "DATE" > SYSDATE-1
GROUP BY ID, TO_CHAR("DATE", 'YYYY-MM-DD')

or truncate the value so that the time component is set to midnight for each date:

SELECT ID,
       TRUNC("DATE"),
       SUM(AMOUNT) AS TOTAL FROM MY_TABLE
WHERE  ID ='23'
AND    "DATE" > SYSDATE-1
GROUP BY ID, TRUNC("DATE")

(Note: DATE is a keyword and cannot be used as an identifier unless you use a quoted-identifier; and you would need to use the quotes, and the exact case, everytime you refer to the column. You would be better to rename the column to something else that is not a keyword.)

user_15092640
Related