How to group every day in PL / SQL?

Question

I am trying to get statistics for every day using PL / SQL.

Each day has several entries, bukkets of errors :-) I want to group them by day.

What am I doing now:

SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage FROM log WHERE (...) GROUP BY MONTH, DAY, errormessage 

The result is ORA-00904: "DAY": invalid identifier (stops in the group).

Any help ?: D

Answer 1

 SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage FROM log WHERE (...) GROUP BY TO_CHAR(dateTime, 'DD'), TO_CHAR(dateTime, 'MM'), errormessage 

Column aliases are not suitable for GROUP BY , you need a full expression.

Answer 2

Your problem is the problem with the area. The GROUP BY is part of the statement table expression and therefore evaluated before the SELECT , that is, the projection. This means that your projected aliases are not available during grouping. This is one of the solutions.

 SELECT DAY, MONTH, errormessage FROM ( SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage FROM log WHERE (...) ) GROUP BY MONTH, DAY, errormessage 

this is different:

 SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage FROM log WHERE (...) GROUP BY TO_CHAR(dateTime, 'MM'), TO_CHAR(dateTime, 'DD'), errormessage 

Custom behavior

Note that some databases (including MySQL, PostgreSQL, and others) allow you to reference column aliases from the projection in the GROUP BY . In more strict SQL dialects (such as Oracle) that should not be possible, though

Answer 3

 SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage FROM log WHERE (...) GROUP by TO_CHAR(dateTime, 'DD'), TO_CHAR(dateTime, 'MM'), errormessage 

you cannot use aliases in a group