SQL Server GROUP BY 進階用法：HAVING、ROLLUP、CUBE 與 GROUPING 函數

GROUP BY 搭配彙總函數 (SUM, AVG, COUNT...) 進行分組統計。進階子句 HAVING、ROLLUP、CUBE 及函數 GROUPING() 能滿足多維分析需求。

基礎語法

SELECT CategoryID, AVG(UnitPrice) AS AvgPrice, COUNT(*) AS Cnt
FROM Products
WHERE UnitPrice > 30
GROUP BY CategoryID
ORDER BY CategoryID DESC

1. DISTINCT 彙總

SELECT CategoryID, AVG(DISTINCT UnitPrice), COUNT(DISTINCT UnitPrice)
FROM Products
WHERE UnitPrice > 30
GROUP BY CategoryID

去除重複單價後再計算平均與計數。

2. HAVING 分組後篩選

SELECT CategoryID, SUM(UnitPrice) AS SumPrice
FROM Products
GROUP BY CategoryID
HAVING SUM(UnitPrice) > 300

WHERE 在分組前篩選列；HAVING 在分組後篩選群組。可搭配 AND/OR/NOT/LIKE。

3. ROLLUP 產生階層式小計/總計

SELECT CategoryID, SUM(UnitPrice) AS SumPrice, GROUPING(CategoryID) AS Grp
FROM Products
GROUP BY CategoryID WITH ROLLUP

• 結果包含各分類小計 + 最後一列總計 (CategoryID 為 NULL)。
• GROUPING(CategoryID) 回傳 1 表示該列為彙總列，0 為明細列。

4. CUBE 產生所有維度組合交叉表

SELECT CategoryID, SupplierID, SUM(UnitPrice) AS SumPrice
FROM Products
GROUP BY CategoryID, SupplierID WITH CUBE

產生：各分類小計、各供應商小計、分類×供應商交叉小計、總計。適用多維報表。

5. 處理 CUBE/ROLLUP 產生的 NULL 標識

SELECT CASE WHEN GROUPING(SupplierID) = 1 THEN '-1' ELSE CAST(SupplierID AS VARCHAR) END AS SupplierID,
       SUM(UnitPrice) AS QtySum
FROM Products
GROUP BY SupplierID WITH CUBE

利用 GROUPING() 判斷是否為彙總列，自訂顯示值 (如 -1、All、總計)。

彙總對照表