5

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"
;
  • It is Oracle 11g – Konrad Jun 19 '15 at 7:59
  • It's also better (and probably more correct) to use GROUP BY a, b than GROUP BY a || ' - ' || b – ypercubeᵀᴹ Jun 19 '15 at 8:20
  • Thanks, it works with group by a, b. Do you know, why "Vertrag" doesn't work in the section GROUP BY? – Konrad Jun 19 '15 at 9:18
  • a_horse_with_no_name has already answered on that. See also a similar question at SO (shameless self-promotion;) : Using 'case expression column' in where clause – ypercubeᵀᴹ Jun 19 '15 at 9:45
5

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.

| improve this answer | |
3

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.
| improve this answer | |
  • I added the word "logically" before the "processed". Because the implementation and actual processing is often different. – ypercubeᵀᴹ Jun 19 '15 at 14:49
1

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.