Why Doesn't Oracle SQL Allow Us to Use Column Aliases in Group by Clauses

Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?

It isn't just Oracle SQL, in fact I believe it is conforming to the ANSI SQL standard (though I don't have a reference for that). The reason is that the SELECT clause is logically processed after the GROUP BY clause, so at the time the GROUP BY is done the aliases don't yet exist.

Perhaps this somewhat ridiculous example helps clarify the issue and the ambiguity that SQL is avoiding:

SQL> select job as sal, sum(sal) as job
  2  from scott.emp
  3  group by job;

SAL              JOB
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600

Can't use column alias in GROUP BY

As mentioned in another answer, You can not add aliases in GROUP BY but you can add aliases in ORDER BY. Also, DATE_FORMAT is MySql function. It is TO_CHAR in Oracle.

So your final query should be as following:

SELECT
    TO_CHAR(TRANS_DATE, 'YYYY-MM') AS MONTH,
    COUNTRY,
    COUNT(*) AS TRANS_COUNT,
    SUM(CASE WHEN STATE = 'approved'  THEN 1 ELSE 0 END) AS APPROVED_COUNT,
    SUM(AMOUNT) AS TRANS_TOTAL_AMOUNT,
    SUM(CASE WHEN STATE = 'approved' THEN AMOUNT ELSE 0 END) AS APPROVED_TOTAL_AMOUNT
FROM TRANSACTIONS
GROUP BY TO_CHAR(TRANS_DATE, 'YYYY-MM'), COUNTRY
ORDER BY MONTH;

Why won't Oracle SQL let us use aliases in conditions?

I think it's just because that is what the SQL standard specifies. I don't agree with gd047 that the alias couldn't be used: the HAVING clause operates on the (intermediate) results of the query i.e. after aggregation, so it would seem pretty straight-forward for the query parser to use the alias to access that result.

You can of course avoid repeating the SUM like this (using the example from the linked question):

SELECT *
from
( SELECT donem, bolge_adi, sehir_tasra "1=S, 2=T", 
         COUNT(DISTINCT mekankodu) "M.SAYISI",
         SUM(b2b_dagitim + b2b_transfer - b2b_iade) satis
  FROM mps_view2
  WHERE donem IN ('200612','200712','200812','200912')
  AND (ob IS NOT NULL OR b2b_ob IS NOT NULL)
  GROUP BY donem, bolge_adi, sehir_tasra
)
WHERE satis > 0
ORDER BY donem, bolge_adi, sehir_tasra

SQL - using alias in Group By

SQL is implemented as if a query was executed in the following order:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

Why can't I use column aliases in the next SELECT expression?

You can use a previously created alias in the GROUP BY or HAVING statement but not in a SELECT or WHERE statement. This is because the program processes all of the SELECT statement at the same time and doesn't know the alias' value yet.

The solution is to encapsulate the query in a subquery and then the alias is available outside.

SELECT stddev_time, max_time, avg_time, min_time, cnt, 
       ROUND(avg_time * cnt, 2) as slowdown
FROM (
        SELECT 
            COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
            MAX(time) as max_time, 
            ROUND(AVG(time), 2) as avg_time, 
            MIN(time) as min_time, 
            COUNT(path) as cnt, 
            path
        FROM 
            loadtime
        GROUP BY
            path
        ORDER BY
            avg_time DESC
        LIMIT 10
   ) X;

SQL not recognizing column alias in where clause

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING
clauses to refer to the column.

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is
evaluated, the column value may not yet have been determined.

So, the following query is illegal:

SQL> SELECT empno AS employee, deptno AS department, sal AS salary
  2  FROM emp
  3  WHERE employee = 7369;
WHERE employee = 7369
      *
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier

SQL>

The column alias is allowed in:

• GROUP BY
• ORDER BY
• HAVING

You could refer to the column alias in WHERE clause in the following cases:

1. Sub-query
2. Common Table Expression(CTE)

For example,

SQL> SELECT * FROM
  2  (
  3  SELECT empno AS employee, deptno AS department, sal AS salary
  4  FROM emp
  5  )
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL> WITH DATA AS(
  2  SELECT empno AS employee, deptno AS department, sal AS salary
  3  FROM emp
  4  )
  5  SELECT * FROM DATA
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL>

Why can't I use an alias for an aggregate in a having clause?

In MS SQL, the only place (I'm aware of) that you can reference aliases is in the ORDER BY clause. The ability to reference aliases in other parts of the query is a feature that many other db platforms have and honestly it annoys me that Microsoft hasn't considered it a useful enough feature to add it.

Group by alias (Oracle)

select
  count(count_col),
  alias_column
from
  (
  select 
    count_col, 
    (select value from....) as alias_column 
  from 
    table
  ) as inline
group by 
  alias_column

Grouping normally works if you repeat the respective expression in the GROUP BY clause. Just mentioning an alias is not possible, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.

To GROUP BY the result of a sub-query, you will have to take a little detour and use an nested query, as indicated above.

---

Related Topics