Search code examples
sqldistinctdate-format

Oracle SQL - group by date_format function

Can someone help me to solve my error please? I am trying to count distinct values in ID_ACCIDENT column and I want them grouped by months (dates are stored in column DATUM in format YYYY/MM/DD).

A the moment I have this code:

SELECT DATE_FORMAT(DATUM, '%Y/%M') as "Month",COUNT (DISTINCT ID_ACCIDENT) AS "Počet nehod" 
FROM RAW_DATA
GROUP BY DATE_FORMAT(DATUM, '%Y/%M');

But it doesn't work - getting error 00904. 00000 - invalid identifier.

Solution

  • How about using to_date since this is Oracle -

    select to_char(to_date(datum, 'yyyy/mm/dd'), 'yyyy/mm')) as month,
COUNT (DISTINCT ID_ACCIDENT) as count
from raw_data
group by to_char(to_date(datum, 'yyyy/mm/dd'), 'yyyy/mm'))