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
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 ?
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.