CODEWITHSUNDEEP
sqloracle-databaseplsql
Jason94
Jason94

Reputation: 13610

How to group by each day in PL/SQL?

Im trying to get statistics for each day with PL/SQL.

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

What Im currently doing:

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

This results in ORA-00904: "DAY": invalid identifier (stops on the group by).

Any help? :D

Upvotes: 9

Views: 25848

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220877

Your problem is a scope problem. The GROUP BY clause is part of the statement's table expression and is thus evaluated before the SELECT clause, i.e. the projection. This means that your projected aliases are not available at grouping time. This is one solution

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 another:

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

Non-standard behaviour

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

Upvotes: 5

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

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 no good for GROUP BY, you need the full expression.

Upvotes: 20

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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 can't use aliases in group by

Upvotes: 2

Related Questions