7

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly?

SELECT
  KOS_VER_ID        AS "Vertrag"
  , WHR_ISO_3_CODE  AS "Waehrung"
  , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN as "Kostenart"
  , SUM (KOS_BETRAG) AS "Summe pro KOA"
FROM
  KOSTEN
  , WAEHRUNG
  , SCHLUESSELTABELLE
WHERE 
  KOSTEN.KOS_VERHI_WHR_ID = WAEHRUNG.WHR_ISO_ID
  AND KOSTEN.KOS_KOA_ST_KZN = SCHLUESSELTABELLE.ST_ID
  AND KOS_VER_ID in (2509, 2510, 2511)
GROUP BY
  KOS_VER_ID
  , WHR_ISO_3_CODE
  , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN
ORDER BY
  "Vertrag"
  , "Kostenart"
;
Konrad
  • 71
  • 1
  • 1
  • 2

4 Answers4

8

Why does the alias not work in the GROUP BY clause?

Because these simply are the syntax rules. A column alias may be used in the ORDER BY clause but not in the GROUP BY clause.

If you look at the syntax diagrams this becomes obvious:

The order by clause is defined as:enter image description here

So after the ORDER BY an expression is allowed, a position (order by 1) or a c_alias which stands for "column alias".

The group by is defined as:

enter image description here

As you can see, no c_alias option after it, so you can't use a column alias.

This is nothing special to Oracle though. This is how it was defined in the SQL standard.

4

The syntax restrictions mentioned by @a_horse_with_no_name are merely a reflection of how the query is logically processed by the SQL database engine:

  1. The FROM clause is processed and all required tables are identified, after possibly resolving view definitions.
  2. The WHERE clause is processed, applying predicates to the table rows.
  3. The GROUP BY clause is processed, applying aggregate functions to the rows remaining after the WHERE.
  4. The HAVING clause is processed, applying additional predicates to the output of GROUP BY.
  5. Only now the SELECT list is processed, retrieving the requested columns and possibly assigning aliases to them.
  6. The ORDER BY clause is processed and, since it comes after the SELECT list processing, it has access to the aliases assigned thereby.
ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
mustaccio
  • 28,207
  • 24
  • 60
  • 76
1

wow ai

Just to keep things fresh, Oracle 23ai began allowing aliases and column positions in the GROUP BY clause.

Alias example:

select initcap(d.dname) as department, count(*) as amount
from   dept d
join   emp e on d.deptno = e.deptno
group by department
having amount > 3;

Column position example:

select initcap(d.dname) as department, count(*) as amount
from   dept d
join   emp e on d.deptno = e.deptno
group by 1
having amount > 3;
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
0

Code evaluation follows the Logical Processing Order of the SELECT statement .

Aliases can be used only if they were introduced in the preceding step. So aliases in the SELECT clause can be used in the ORDER BY but not the GROUP BY clause.

Reference: Microsoft T-SQL Documentation for further reading.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Hope this helps.

Also, Related discussion

Pankil Shah
  • 109
  • 1