Oracle SQL - group by date_format function-CodePudding

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.

CodePudding user response：

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'))

CodePudding user response：

if DATUM is date then

SELECT 
  TRUNC(DATUM, 'MONTH')  as "Month",
  COUNT (DISTINCT ID_ACCIDENT) AS "Počet nehod" 
FROM RAW_DATA
GROUP BY TRUNC(DATUM, 'MONTH');

if DATUM is string with YYYY/MM/DD format then

SELECT 
  SUBSTR(datum,1,7), 
  COUNT(DISTINCT id) 
FROM raw_data 
GROUP BY substr(datum,1,7)