Reuse calculated column in HAVING clause

Question

Please look at the following query

SELECT 
    product_id, SUM(unit_price * quantity) Total
FROM 
    tblOrders
GROUP BY 
    product_id
HAVING
    SUM(unit_price * quantity) > 1000

The above query works fine. But you can see in there query, Total is a calculated column, and the same calculation is used again in the HAVING clause.

How can I avoid using this same calculation in the HAVING clause?

Answer 1

Well, after doing some more study in this subject last couple of days, i have found my answer. In fact, my query what i posted in the question is completely correct, just we can write that query in other way(s), but we can't avoid to reuse the calculation actually, even though we make the query nested like below :

SELECT *
FROM
(
    SELECT product_id,
           SUM(unit_price * quantity) Total
    FROM tblOrders
    GROUP BY product_id
) A
WHERE A.Total > 1000;

This is just because the order of logical processing of the SELECT statement. Look at the below order :

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

Notice the order of SELECT statement, any column aliases or derived columns defined in SELECT statement cannot be referenced by preceding clauses whose orders are less than 8. You can get more information about it Here