MySQL SUM() 聚合函數詳解：總和計算、分組統計與員工表實戰

函數語法

SUM([DISTINCT] expr)

返回數值列總和，忽略 NULL。DISTINCT 去重後求和（少用）。無匹配行返回 NULL。

員工表實戰數據

+------+------+------------+--------------------+
| 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                |
+------+------+------------+--------------------+

全表總和

mysql> SELECT SUM(daily_typing_pages) FROM employee_tbl;
+-------------------------+
| SUM(daily_typing_pages) |
+-------------------------+
| 1610                    |
+-------------------------+

分組統計：每人總打字量

mysql> SELECT name, SUM(daily_typing_pages) FROM employee_tbl GROUP BY name;
+------+-------------------------+
| name | SUM(daily_typing_pages) |
+------+-------------------------+
| Jack | 270                     |
| Jill | 220                     |
| John | 250                     |
| Ram  | 220                     |
| Zara | 650                     |
+------+-------------------------+

進階組合

• 多聚合：SELECT name, SUM(pages), AVG(pages), COUNT(*) FROM employee_tbl GROUP BY name
• 條件聚合：SELECT SUM(CASE WHEN work_date > '2007-05-01' THEN pages ELSE 0 END) FROM employee_tbl
• ROLLUP 超級聚合：SELECT name, SUM(pages) FROM employee_tbl GROUP BY name WITH ROLLUP（最後一行顯示總計）

性能與避坑

• 大表聚合利用索引：WHERE 篩選後聚合比全表快
• SUM(col) 配合 COALESCE(SUM(col),0) 避免 NULL 導致應用層報錯
• 浮點數列求和可能有精度誤差，金額類用 DECIMAL 類型