MySQL SUM() 函數教程：求和統計與分組匯總

MySQL SUM() 函數教程：求和統計與分組匯總

SUM() 聚合函數返回數值列的總和，忽略 NULL 值。適用於銷售額、數量、頁數等累加場景。

範例數據

employee_tbl 表，daily_typing_pages 欄位。

1. 全表總和

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

2. 分組求和

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

3. 組合多聚合報表

SELECT 
    name,
    COUNT(*) AS work_days,
    SUM(daily_typing_pages) AS total_pages,
    AVG(daily_typing_pages) AS avg_pages,
    MIN(daily_typing_pages) AS min_pages,
    MAX(daily_typing_pages) AS max_pages
FROM employee_tbl
GROUP BY name
ORDER BY total_pages DESC;

4. 條件求和（條件聚合）

統計某人打字超過 200 頁的天數總頁數：

SELECT 
    name,
    SUM(CASE WHEN daily_typing_pages > 200 THEN daily_typing_pages ELSE 0 END) AS high_pages_sum
FROM employee_tbl
GROUP BY name;

5. NULL 與溢出

• 忽略 NULL。
• 整數列求和可能溢出，MySQL 會自動提升為 BIGINT 或 DECIMAL。