SQL HAVING

SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

Why can’t the SQL above be written in the following format?

SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING ordertotal >= 50
ORDER BY ordertotal;

We need to understand the execution order of SQL.
The logical processing order of an SQL query is typically:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

From this order, it’s clear that the HAVING clause is executed before the SELECT clause. Therefore, when the HAVING clause is processed, the alias defined in the SELECT clause (e.g., ordertotal) has not yet been calculated or assigned, so it cannot be directly referenced in the HAVING clause. This is why the second version of your query will result in an error.

Subquery approach:

SELECT order_num, ordertotal
FROM (
    SELECT order_num, SUM(quantity * item_price) AS ordertotal
    FROM orderitems
    GROUP BY order_num
) AS subquery
WHERE ordertotal >= 50
ORDER BY ordertotal;