SUM() 函數 (SQL SUM() Function)

SUM() 是一個聚合函數 (Aggregate Function)，用於計算數值欄位的總和。它會忽略 NULL 值，只對非 NULL 的數值進行加總。

SUM() 語法 (Syntax)

SELECT SUM(column_name) FROM table_name [WHERE condition];

SUM() 用法範例 (Example)

orders 資料表：

O_IdPriceCustomer
11000張一
22000王二
3500李三
41300張一
51800王二

範例 1：計算所有訂單的金額總和

SELECT SUM(Price) AS TotalAmount
FROM orders;

結果：

TotalAmount
6600

範例 2：搭配 WHERE 條件

計算「張一」的訂單金額總和：

SELECT SUM(Price) AS TotalAmount
FROM orders
WHERE Customer = '張一';

結果：

TotalAmount
2300

SUM() 搭配 GROUP BY

使用 GROUP BY 對每個群組分別加總：

SELECT Customer, SUM(Price) AS TotalAmount
FROM orders
GROUP BY Customer;

結果：

CustomerTotalAmount
張一2300
王二3800
李三500

SUM() 搭配 HAVING

使用 HAVING 篩選加總後的結果：

SELECT Customer, SUM(Price) AS TotalAmount
FROM orders
GROUP BY Customer
HAVING SUM(Price) > 1000;

結果：

CustomerTotalAmount
張一2300
王二3800

SUM() 搭配 CASE (條件加總)

使用 CASE 進行條件式加總：

SELECT
    SUM(CASE WHEN Customer = '張一' THEN Price ELSE 0 END) AS ZhangYi_Total,
    SUM(CASE WHEN Customer = '王二' THEN Price ELSE 0 END) AS WangEr_Total
FROM orders;

結果：

ZhangYi_TotalWangEr_Total
23003800

SUM() 與 NULL 值

SUM() 會忽略 NULL 值，只加總非 NULL 的數值：

假設資料：

O_IdPrice
11000
2NULL
3500
SELECT SUM(Price) AS Total FROM orders;
-- 結果：1500 (NULL 被忽略)

💡 注意： 如果所有值都是 NULL，SUM() 會返回 NULL 而非 0。

-- 確保返回 0 而非 NULL
SELECT COALESCE(SUM(Price), 0) AS Total FROM orders;

SUM(DISTINCT column_name)

計算不重複值的總和：

SELECT SUM(DISTINCT Price) AS UniqueTotal
FROM orders;
-- 只加總不重複的 Price 值

SUM() 搭配表達式

可以對計算結果進行加總：

-- 計算含稅總金額 (假設稅率 5%)
SELECT SUM(Price * 1.05) AS TotalWithTax
FROM orders;

多個 SUM() 同時使用

SELECT
    SUM(Price) AS TotalPrice,
    SUM(Quantity) AS TotalQuantity,
    SUM(Price * Quantity) AS GrandTotal
FROM order_details;

SUM() 與窗口函數

使用窗口函數計算累計總和：

SELECT
    O_Id,
    Customer,
    Price,
    SUM(Price) OVER (ORDER BY O_Id) AS RunningTotal,
    SUM(Price) OVER (PARTITION BY Customer ORDER BY O_Id) AS CustomerRunningTotal
FROM orders;

相關主題