sql - 哪些类别的平均收入高于整体平均水平

Question

如何通过大于条件？我收到一个错误，因为列名“平均”无效。

这是我的代码：

SELECT P.prod_cat, AVG(total_amt) AS average 
FROM Transactions T JOIN 
     prod_cat_info P 
     ON T.prod_cat_code = P.prod_cat_code
WHERE average > AVG(total_amt)
GROUP BY prod_cat

标签： sqlsql-server

Answer 1

由于您试图将所有行与单个值进行比较，因此您应该确保只获得该单个值一次。以下代码在我的一个较大的本地数据库中具有不错的性能（表名已更改以保护无辜者）：

WITH CTE_Overall_Avg AS (SELECT AVG(total_amt) AS overall_avg FROM Transactions)
SELECT
    TX.prod_cat,
    AVG(TX.total_amt) AS prod_cat_avg,
    MAX(OA.overall_avg) AS overall_avg
FROM
    Transactions TX
INNER JOIN CTE_Overall_Avg OA ON 1 = 1
GROUP BY
    TX.prod_cat
HAVING
    AVG(TX.total_amt) > OA.overall_avg