CODEWITHSUNDEEP
oracle-database
Abdusoli
Abdusoli

Reputation: 659

Group by TIMESTAMP`S Date in Oracle

I am trying to group by Timestamp`s Date in oracle so far I used to_char. But I need another way. I tried like below:

    SELECT d.summa,
           d.FILIAL_CODE,
           to_char(d.DATE_ACTION, 'YYYY-MM-DD')
    FROM table1 d
    WHERE d.action_id = 2
      AND d.date_action Between to_date('01.01.2020', 'dd.mm.yyyy') AND to_date('01.03.2020', 'dd.mm.yyyy')
    GROUP BY to_char(d.DATE_ACTION, 'YYYY-MM-DD')

table1

-----------------------------------------------------
summa      |  filial_code    | date_action
--------------------------------------------------
100000.00  |  2100           | 2016-09-13 11:04:32
320000.12  |  3200           | 2016-09-12 21:04:58
400000.00  |  2100           | 2016-09-13 15:12:45
510000.12  |  3200           | 2016-09-15 09:30:58
------------------------------------------------------

I need like below

-------------------------------------------
summa      |  filial_code    | date_action
------------------------------------------
500000.00  |  2100           | 2016-09-13 
320000.12  |  3200           | 2016-09-12 
510000.12  |  3200           | 2016-09-15 
------------------------------------------

But I need except to_char function. I tried trunc but i could not do that

Upvotes: 0

Views: 406

Answers (1)

Peter Lang
Peter Lang

Reputation: 55524

Using TRUNC should actually convert it to a date and remove the time part, but you also need to handle your other columns. Either group by them or use an aggregation function:

SELECT SUM( d.summa ) AS summa,
       d.FILIAL_CODE,
       TRUNC(d.DATE_ACTION) AS date_action
FROM table1 d
WHERE d.action_id = 2
  AND d.date_action Between to_date('01.01.2020', 'dd.mm.yyyy')
                        AND to_date('01.03.2020', 'dd.mm.yyyy')
GROUP BY TRUNC(d.DATE_ACTION), d.FILIAL_CODE

Upvotes: 3

Related Questions