sql - ORA-00904: invalid identifier issue with alias in SELECT

Question

Oracle 11g is giving me an invalid identifier error with the following code:

SELECT idproduct, SUM(quantity) AS amnt_sold
FROM [table]
GROUP BY idproduct
HAVING amnt_sold >= 3

By deduction, the error is with the HAVING statement and the alias I've used, but I can't figure out for the life of me what the issue is, even after some researching.

question from:https://stackoverflow.com/questions/65908433/ora-00904-invalid-identifier-issue-with-alias-in-select

Answer 1

The column alias isn't available in the HAVING clause. (Just as specified by ANSI/ISO SQL.)

Either put the SUM() in the HAVING clause:

SELECT idproduct, SUM(quantity) AS amnt_sold
FROM [table]
GROUP BY idproduct
HAVING SUM(quantity) >= 3

Or, wrap the query up in a derived table, and then use the amnt_sold column name.

SELECT idproduct, amnt_sold
FROM
(
  SELECT idproduct, SUM(quantity) AS amnt_sold
  FROM [table]
  GROUP BY idproduct
) dt
WHERE amnt_sold >= 3