MySQL GROUP BY 子句入門教學：分組統計與聚合函式搭配

GROUP BY 子句將查詢結果依指定欄位分組，配合 COUNT、SUM、AVG 等聚合函式可產出各群組的統計值。本文以 employee_tbl 示範計算每位員工的工作天數。

測試資料

mysql> SELECT * FROM employee_tbl;
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
| 1    | John | 2007-01-24 | 250                |
| 2    | Ram  | 2007-05-27 | 220                |
| 3    | Jack | 2007-05-06 | 170                |
| 3    | Jack | 2007-04-06 | 100                |
| 4    | Jill | 2007-04-06 | 220                |
| 5    | Zara | 2007-06-06 | 300                |
| 5    | Zara | 2007-02-06 | 350                |
+------+------+------------+--------------------+

錯誤示範：無 GROUP BY 只能得總筆數

mysql> SELECT COUNT(*) FROM employee_tbl;
+----------+
| COUNT(*) |
+----------+
| 7        |
+----------+

正確寫法：GROUP BY name 統計每人筆數

mysql> SELECT name, COUNT(*) 
    -> FROM employee_tbl 
    -> GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| Jack | 2        |
| Jill | 1        |
| John | 1        |
| Ram  | 1        |
| Zara | 2        |
+------+----------+

進階應用

• 多欄位分組：GROUP BY name, work_date 依姓名與日期雙重分組。
• 搭配 HAVING 過濾群組：HAVING COUNT(*) > 1 只保留出現超過 1 次的員工。
• SELECT 清單限制：非聚合欄位必須出現在 GROUP BY 中（MySQL 嚴格模式下強制，否則會任意取值）。
• 排序：GROUP BY name ORDER BY COUNT(*) DESC 依筆數遞減排序。

效能提示

大表分組統計可考慮：建立覆蓋索引、使用總結表預先聚合、或利用資料倉儲工具離線計算。