Use column alias in GROUP BY

aliasgroup byoracle

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"
;

Best Answer

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.

Related Question