SQL GROUP BY 子句完整指南：分組彙總語法與實戰範例

核心用途

GROUP BY 將多筆記錄依指定欄位分組，搭配彙總函數產出摘要報表。

語法結構

SELECT column1, column2, ..., aggregate_function(expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ...;

規則：SELECT 清單中未包裹在彙總函數的欄位，必須出現在 GROUP BY 中。

範例 1：SUM 函數 - 部門銷售總額

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

範例 2：COUNT 函數 - 各類別商品數量

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

範例 3：多欄位分組 - 區域與業務員業績

SELECT region, salesperson, SUM(amount) AS total_amount
FROM orders
GROUP BY region, salesperson;

進階技巧

• ROLLUP / CUBE：產生小計與總計（SQL Server 支援）
• GROUPING SETS：自訂分組組合
• HAVING 進階篩選：HAVING COUNT(*) > 5 AND AVG(score) > 80

常見錯誤避坑

• SELECT 包含非彙總、非 GROUP BY 欄位 → 語法錯誤
• 忘記在 GROUP BY 列出所有非彙總欄位
• 對大量資料未建索引導致 Hash Match/ Sort 效能差