GROUP BY 將相同值的資料列分組,搭配彙總函式 (SUM, COUNT, AVG...) 產出摘要報表。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;
SELECT Customer, SUM(OrderPrice) AS TotalAmount
FROM Orders
GROUP BY Customer;
+----------+-------------+
| Customer | TotalAmount |
+----------+-------------+
| Hansen | 2000 |
| Nilsen | 1700 |
| Jensen | 2000 |
+----------+-------------+
SELECT Customer, YEAR(OrderDate) AS Year, SUM(OrderPrice)
FROM Orders
GROUP BY Customer, YEAR(OrderDate);
SELECT 清單中未使用彙總函式的欄位,必須出現在 GROUP BY 中。WHERE 在分群前過濾,HAVING 在分群後過濾。ONLY_FULL_GROUP_BY 模式嚴格執行此規則。-- 錯誤:name 不在 GROUP BY 且非彙總
SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders GROUP BY Customer;
-- 正確:加入彙總或納入 GROUP BY
SELECT Customer, MAX(OrderDate), SUM(OrderPrice) FROM Orders GROUP BY Customer;