Invalid identifier oracle sql

Question

This error seems to appear a lot and I've had mixed answers such as changing names, but I couldn't see any issues with my query regarding this. Perhaps I have not spotted something?

For example:

select
  sport,
  sum(turn_over) as turnover,
  count(distinct(user_no)) as users,
  sum(turn_over) / count(distinct(user_no)) as average,
  extract(
    month
    from
      ref_date
  ) as outer_month,
  extract(
    year
    from
      ref_date
  ) as outer_year
from
  dw_unica.T_DAILY_SB
group by
  sport,
  outer_month,
  outer_year;

Gives the following error:

ORA-00904: "OUTER_YEAR": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 61 Column: 271

Answer 1

You cannot use aliases defined in the SELECT clause in the GROUP BY clause as the SELECT clause is processed after the GROUP BY clause.

You need to GROUP BY the underlying function calls rather than the aliases:

select sport,
       sum(turn_over) as turnover,
       count(distinct user_no) as users,
       sum(turn_over) / count(distinct user_no) as average,
       extract(month from ref_date) as outer_month,
       extract(year from ref_date) as outer_year
from   dw_unica.T_DAILY_SB
group by
       sport,
       extract(month from ref_date),
       extract(year from ref_date);