Alias in where clause and the group by

Question

I have a query where I select one column which I group and the sum of prices which I want to alias in order to use them in the condition.

The problem is in WHERE as it cant be aliased there but even the Parenthesis/Subselect (Which is the answer to the similar question) example does not help because at the end order by would not work.

Here is my query:

SELECT product_status, SUM(list_price) as sum_prices
FROM OE.PRODUCT_INFORMATION
WHERE (LOWER(product_status) NOT LIKE '%orderable%') AND (sum_prices >= 4000)
GROUP BY product_status;

Output:

ORA-00904: "SUM_PRICES": invalid identifier

This could be helpful: https://www.w3schools.com/sql/sql_having.asp — Namandeep_Kaur, May 13 '21 at 23:18

MT0 · Accepted Answer · 2021-05-13T23:25:32.737

You cannot use an alias in the WHERE clause; you would need to use the full un-aliased expression instead. However, that will not work here as the expression you have aliased is an aggregation and the WHERE filters are evaluated before the GROUP BY and you need to filter on the result of the aggregation after the GROUP BY; that is what the HAVING clause is for, filtering after GROUPing.

So, either use HAVING and repeat the aggregation expression:

SELECT product_status,
       SUM(list_price) as sum_prices
FROM   OE.PRODUCT_INFORMATION
WHERE  LOWER(product_status) NOT LIKE '%orderable%'
GROUP BY product_status
HAVING SUM(list_price) >= 4000;

Or wrap it in a sub-query then you can use the alias in the outer query.

SELECT *
FROM   (
  SELECT product_status,
         SUM(list_price) as sum_prices
  FROM   OE.PRODUCT_INFORMATION
  WHERE  LOWER(product_status) NOT LIKE '%orderable%'
  GROUP BY product_status
)
WHERE  sum_prices >= 4000;

Alias in where clause and the group by

1 Answers1