Oracle date formatting error - ORA-00933: SQL command not properly ended

Question

I am trying to execute a simple query (ORACLE DB) to display two date columns (START_TIME & END_TIME) along with total counts along with percentage and filtered by a month followed by year the data type defined in the for the two date fields in db as date, i am trying to display the data in the below format

sample data no time stamp

start_time - 01.12.2020 
end_time - 02.12.2020

Query

SELECT 
to_char(cast(START_TIME as date),'YYYY') as START_BY_YEAR ,
to_char(cast(END_TIME as date),'YYYY') as END_BY_YEAR ,
to_char(cast(START_TIME as date),'MM.YYYY')as START_BY_MONTH,
to_char(cast(END_TIME as date),'MM.YYYY') as END_BY_MONTH,
to_char(cast(START_TIME as date),'DD.MM.YYYY') as START_BY_DAY,
to_char(cast(END_TIME as date),'DD.MM.YYYY') as END_BY_DAY,
COUNT(*) as count,
round(100*ratio_to_report(count(*)) over (), 4) percentage  
FROM SCHEMA_NAME.TABLE_NAME
WHERE to_char(cast(START_TIME as date),'MM.YYYY')='12.2020'
and to_char(cast(END_TIME as date),'MM.YYYY')='12.2020'
GROUP BY 
START_BY_YEAR,
END_BY_YEAR,
START_BY_MONTH,
END_BY_MONTH,
START_BY_DAY,
END_BY_DAY
order by 
START_BY_YEAR desc,
END_BY_YEAR desc,
START_BY_MONTH desc,
END_BY_MONTH desc,
START_BY_DAY desc,
END_BY_DAY desc

I am getting error message

ORA-00904: "END_BY_DAY": invalid identifier

904. 00000 - "%s: invalid identifier" *Cause:
     *Action: Error at Line: 19 Column: 1

where exactly i am getting error is not clear

expected result set

START_BY_YEAR END_BY_YEAR START_BY_MONTH END_BY_MONTH START_BY_DAY END_BY_DAY
2012          2012        12.2012        12.2012      01.12.2020   02.12.2020   

Please note i have expanded the code for easily readable purpose.

Suggestion ?

Answer 1

To solve the error, you have to remove the aliases from the GROUP BY clause. Also, if your table has date columns, you don't need CASTs and the query can be re-wrtten as:

SELECT 
    to_char(START_TIME,'YYYY') START_BY_YEAR ,
    to_char(END_TIME,'YYYY') END_BY_YEAR ,
    to_char(START_TIME,'MM.YYYY') START_BY_MONTH,
    to_char(END_TIME,'MM.YYYY') END_BY_MONTH,
    to_char(START_TIME,'DD.MM.YYYY') START_BY_DAY,
    to_char(END_TIME,'DD.MM.YYYY') END_BY_DAY,
    COUNT(*) as count,
    round(100*ratio_to_report(count(*)) over (), 4) percentage  
FROM TABLE_NAME
WHERE to_char(START_TIME,'MM.YYYY')='12.2020'
  and to_char(END_TIME,'MM.YYYY')='12.2020'
GROUP BY 
  to_char(START_TIME,'YYYY') ,
  to_char(END_TIME,'YYYY') ,
  to_char(START_TIME,'MM.YYYY') ,
  to_char(END_TIME,'MM.YYYY') ,
  to_char(START_TIME,'DD.MM.YYYY') ,
  to_char(END_TIME,'DD.MM.YYYY') 
order by 
    START_BY_YEAR desc,
    END_BY_YEAR desc,
    START_BY_MONTH desc,
    END_BY_MONTH desc,
    START_BY_DAY desc,
    END_BY_DAY desc

With a table like this:

create table table_name as
(
    select date '2020-12-01' start_time,
           date '2020-12-02' end_time
    from dual
);

The query gives:

START_BY_YEA END_BY_YEAR  START_BY_MON END_BY_MONTH START_BY_DAY END_BY_DAY   COUNT PERCENTAGE
------------ ------------ ------------ ------------ ------------ ------------ ----- ----------
2020         2020         12.2020      12.2020      01.12.2020   02.12.2020       1        100
1 row selected.

Answer 2

First check the partial result of:

select cast(END_TIME as date),'DD.MM.YYYY') as END_BY_DAY
FROM SCHEMA_NAME.TABLE_NAME

from the results you can learn about the NLS format of your machin and wrap it with custom to_char format.