How to reuse calculated column in HAVING clause?

问题:

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?

回答1:

Here is another way is to avoid further calculation using Subquery instead use of HAVING Clause :

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

回答2:

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

回答3:

You need an aggregation function. I would guess that you really want:

select product_id, sum(unit_price * quantity) as Total
from tblOrders
group by product_id
having sum(unit_price * quantity) > 1000;

Your original query is syntactically incorrect and should return a compiler error on SQL Server -- because unit_price * quantity is not a group by key.