使用彙總函式

3 分鐘

T-SQL 提供彙總函數 (例如 SUM、MAX 和 AVG) 來執行計算，可接受多個值並傳回單一結果。

使用彙總函數

我們看過的大部分查詢都使用 WHERE 子句來篩選資料列，一次只處理一個資料列。傳回的每個資料列對應至原始資料集的一個資料列。

SQL Server 提供許多彙總函數。在本節中，我們將看一下最常用的函數，例如 SUM、MIN、MAX、AVG 和 COUNT。

使用彙總函數時，您需要考慮下列幾點：

彙總函數傳回單一 (純量) 值，可用在 SELECT 陳述式中幾乎任何可使用單一值的地方。例如，SELECT、HAVING 和 ORDER BY 子句中可以使用這些函數。但不能用在 WHERE 子句中。
彙總函數忽略 NULL，但使用 COUNT (*) 時例外。
除非您使用 AS 來提供別名，否則 SELECT 清單中的彙總函數沒有資料行標題。
SELECT 清單中的彙總函數處理所有傳給 SELECT 作業的資料列。如果沒有 GROUP BY 子句，則會摘要滿足 WHERE 子句中任何篩選條件的所有資料列。您將在下一個主題中深入了解 GROUP BY。
除非使用 GROUP BY，否則彙總函數與相同 SELECT 清單中的函數未包含的資料行，不應該合併。

為了延伸超越內建函數，SQL Server 透過 .NET Common Language Runtime (CLR)，提供使用者定義彙總函數的機制。該主題已超出本課程模組的範圍。

內建彙總函數

如前文所述，Transact-SQL 提供許多內建彙總函數。常用函數包括：

函數名稱

語法

說明

SUM

SUM(expression)

總計資料行中的所有非 NULL 數值。

平均

AVG(expression)

算出資料行中所有非 NULL 數值的平均值 (總和/計數)。

MIN

MIN(expression)

傳回最小數字、最早日期/時間，或最先出現的字串 (根據定序排序規則)。

MAX

MAX(expression)

傳回最大數字、最晚日期/時間，或最後出現的字串 (根據定序排序規則)。

COUNT 或 COUNT_BIG

COUNT(*) 或 COUNT(expression)

加上 (*) 將計算所有資料列的數目，包括含有 NULL 值的資料列。將資料行指定為 expression 時，將傳回該資料行的非 NULL 資料列計數。 COUNT 傳回 int，COUNT_BIG 傳回 big_int。

若要在 SELECT 子句中使用內建彙總，請看以下在 MyStore 範例資料庫中的範例：

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

此查詢的結果如下所示：

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

請注意，上述範例摘要 Production.Product 資料表的所有資料列。我們可以輕鬆修改查詢，新增 WHERE 子句，以傳回特定類別中產品的平均、最小和最大價格，如下所示：

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

在 SELECT 子句中使用彙總時，SELECT 清單中參考的所有資料行都必須當做彙總函數的輸入，或在 GROUP BY 子句中參考。

請看下列查詢，其中嘗試在彙總結果中包含 ProductCategoryID 欄位：

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

執行此查詢會產生下列錯誤

訊息 8120，層級 16，狀態 1，行 1

資料行 'Production.ProductCategoryID' 在 select 清單中無效，因為未出現在彙總函數或 GROUP BY 子句中。

此查詢將所有資料列視為單一彙總群組。因此，所有資料行都必須當做彙總函數的輸入。

在先前的範例中，我們彙總數值資料，例如上一個範例中的價格和數量。某些彙總函數也可以用來摘要日期、時間和字元資料。下列範例示範如何對日期和字元使用彙總：

此查詢使用 MIN 和 MAX，依名稱傳回第一個和最後一個公司：

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

此查詢依資料庫的定序順序，傳回 CompanyName 的第一個和最後一個值，在此案例中是依字母順序：

MinCustomer

MaxCustomer

A Bike Store

Yellow Bicycle Company

其他函數可以與彙總函數巢狀在一起。

例如，下列範例在評估 MIN 和 MAX 之前，使用 YEAR 純量函數，只傳回訂單日期的年份部分：

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

最早

DISTINCT 與彙總函數一起使用

您應該懂得在 SELECT 子句中使用 DISTINCT 來移除重複的資料列。 DISTINCT 與彙總函數一起使用時，在計算摘要值之前，將從輸入資料行中移除重複的值。 DISTINCT 適用於摘要個別值的出現次數，例如訂單資料表中的客戶。

下列範例傳回已下訂單的客戶數目，而不論已下多少訂單：

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) 只是計算多少資料列在資料行中有值。如果沒有 NULL 值，則 COUNT(<some_column>) 與 COUNT(*) 相同。 COUNT (DISTINCT <some_column>) 計算資料行中有多少不同的值。

使用彙總函數來處理 NULL

請務必注意資料中可能存在 NULL，以及 Null 與 T-SQL 查詢元件如何互動，包括彙總函數。有幾項考量需要注意：

T-SQL 彙總函數會忽略 NULL，但加上 (*) 選項的 COUNT 例外。例如，SUM 函數只合計非 NULL 值。 NULL 不會評估為零。 COUNT(*) 計算所有資料列的數目，而不論任何資料行中的值或非值。
資料行中存在 NULL 可能導致 AVG 計算不正確，而只合計已填入的資料列，再將該總和除以非 NULL 資料列數目。 AVG(<column>) 和 (SUM(<column>)/COUNT(*)) 的結果可能不同。

例如，請看以下名為 t1 的資料表：

NULL

針對 AVG 如何處理 NULL，以及您可能如何以 SUM/COUNT(*) 計算資料行來計算平均值，此查詢指出這兩者的差異：

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

結果為：

sum_nonnulls

count_all_rows

count_nonnulls

平均值

arith_average

150

在此結果集，名為 average 的資料行是彙總，在內部算出總和 150，再除以資料行 c2 中的非 null 值計數。計算結果為 150/5，即 30。名為 arith_average 的資料行明確將總和除以所有資料列的計數，因此計算結果為 150/6，即 25。

如果您需要摘要所有資料列，而不論是否為 NULL，請考慮將 NULL 換成彙總函數不會忽略的另一個值。為此，您可以使用 COALESCE 函數。

使用彙總函式

使用彙總函數

內建彙總函數

DISTINCT 與彙總函數一起使用

使用彙總函數來處理 NULL

意見反應