SUM(expr) 返回指定數值列的總和,忽略 NULL 值。若無匹配行則返回 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 |
+----+------+------------+--------------------+
SELECT SUM(daily_typing_pages) FROM employee_tbl;
-- 結果:1610
SELECT name, SUM(daily_typing_pages) AS total_pages
FROM employee_tbl
GROUP BY name;
+------+--------------+
| name | total_pages |
+------+--------------+
| Jack | 270 |
| Jill | 220 |
| John | 250 |
| Ram | 220 |
| Zara | 650 |
+------+--------------+
SELECT name,
SUM(CASE WHEN work_date >= '2007-05-01' THEN daily_typing_pages ELSE 0 END) AS may_jun_pages
FROM employee_tbl
GROUP BY name;
SELECT name,
SUM(daily_typing_pages) AS total_pages,
COUNT(*) AS record_cnt,
AVG(daily_typing_pages) AS avg_pages
FROM employee_tbl
GROUP BY name;
SELECT name, work_date, daily_typing_pages,
SUM(daily_typing_pages) OVER (PARTITION BY name ORDER BY work_date) AS running_total
FROM employee_tbl;
SUM 自動忽略 NULL,但 SUM(COALESCE(col,0)) 寫法更明確。CAST(col AS BIGINT) 或 DECIMAL。SUM 無法用索引,但配合 WHERE 篩選可走索引減少掃描行。SUM() 是最基礎也最常用的聚合函數,配合 GROUP BY、CASE WHEN、窗口函數可覆蓋 90% 以上統計報表需求。掌握其 NULL 處理、類型溢位與索引配合細節,能寫出更健壯的查詢。