SQL Server 計算欄位總和與平均值：HAVING 進階用法與 UDF 拆解技巧

在單一查詢中同時需要「群組總和」與「全域平均」時，直接嵌套聚合函數會報錯 Msg 130: Cannot perform an aggregate function on an expression containing an aggregate。解決方案：拆解為表值函數 (TVF) + 標量函數。

場景：找出總銷售額超過 25000 的客戶訂單

SELECT c.CustomerID, soh.SalesOrderID, SUM(sod.LineTotal) AS SubTotal
FROM Sales.Customer c
JOIN Sales.SalesOrderHeader soh ON soh.CustomerID = c.CustomerID
JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY c.CustomerID, soh.SalesOrderID
HAVING SUM(sod.LineTotal) > 25000
ORDER BY c.CustomerID, soh.SalesOrderID;

需求：同一結果集顯示每筆訂單金額佔全域平均比率

無法直接 AVG(SUM(...))，需拆解：

步驟 1：建立表值函數回傳各訂單總額

CREATE FUNCTION dbo.SalesTotals_TVF()
RETURNS TABLE
AS RETURN (
  SELECT SalesOrderID, SUM(LineTotal) AS TotalSale
  FROM Sales.SalesOrderDetail
  GROUP BY SalesOrderID
);

步驟 2：建立標量函數計算全域平均

CREATE FUNCTION dbo.AvgSale_Scalar()
RETURNS MONEY
AS BEGIN
  DECLARE @avg MONEY;
  SELECT @avg = AVG(TotalSale) FROM dbo.SalesTotals_TVF();
  RETURN @avg;
END;

步驟 3：組合查詢

DECLARE @avg MONEY = dbo.AvgSale_Scalar();
SELECT
  st.SalesOrderID,
  st.TotalSale,
  @avg AS AverageSale,
  st.TotalSale / @avg AS Ratio,
  CASE
    WHEN st.TotalSale / @avg > 1 THEN 'Above Average'
    WHEN st.TotalSale / @avg < 1 THEN 'Below Average'
    ELSE 'Average'
  END AS Level
FROM dbo.SalesTotals_TVF() st;

關鍵點總結

• HAVING 用於群組過濾，需配合 GROUP BY；無群組時改用 WHERE。
• 巢狀聚合 (AVG(SUM(...))) 禁止，必須拆解為多步驟 (TVF/CTE/暫存表/變數)。
• 表值函數 (TVF) 可像表一樣 JOIN，效能優於標量函數逐行調用。
• MySQL 8.0+ 可用 WITH cte AS (...) SELECT ..., AVG(total) OVER () FROM cte 窗口函數一步完成。