彙總查詢

Aggregate functions allow you to apply calculations on values in a column. Adding the GROUP BY clause allows you to provide aggregate on subsets of the data.

Aggregate Functions

當使用彙總函式時要先知道的是：這些函式都會忽略 NULL 欄位值。 底下是 SQL 2008 支援的彙總函式：

• AVG ：
• COUNT ：
• COUNT_BIG ：同 COUNT 函式，只是回傳型別為 bigint 。
• MAX ：
• MIN ：
• SUM ：
• CHECKSUM_AGG ：傳回群組中各個值的總和檢查碼。
• STDEV ：標準差 (standard deviation )
• STDEVP ：擴展標準差 (standard deviation for the population)
• VAR ：變異數 (variance)
• VARP ：擴展變異數 (variance for the population)
• GROUPING ：是否彙總 GROUP BY 清單中指定的資料行運算式。

以下針對幾特定函式簡列說明：

STDEV（標準差）

簡單來說，標準差是一組數值自平均值分散開來的程度的一種測量觀念。 一個較大的標準差，代表大部分的數值和其平均值之間差異較大；一個較小的標準差，代表這些數值較接近平均值。 例如：{3, 5, 9, 11} 和 {5, 6, 8, 9} 這二組數的平均值都是 7 ，但是第一組的標準差為 3.65 ，第二組的標準差為 1.82 。

DECLARE @AgeList1 table( age int NOT NULL);
DECLARE @AgeList2 table( age int NOT NULL);

insert @AgeList1 values (3),(5),(9),(11)
insert @AgeList2 values (5),(6),(8),(9)

SELECT AVG(age) From @AgeList1		--7
SELECT AVG(age) From @AgeList2		--7

SELECT STDEV(age) From @AgeList1	--3.65148371670111
SELECT STDEV(age) From @AgeList2	--1.82574185835055

STDEVP（變異數）

變異數是由資料值與期望值的差異計算而來，也是用來描述一組數的密度(變化程度)。變異數的平方根＝標準差。

SELECT VAR(age) From @AgeList1	--13.3333333333333
SELECT VAR(age) From @AgeList2	--3.33333333333333

CHECKSUM_AGG

CHECKSUM_AGG 會傳回群組中各個值的總和檢查碼，常用在判斷集合中的資料是有被異動過。

select * from Emp
SELECT DepNo, CHECKSUM_agg(EmpNo) CheckValue FROM Emp GROUP BY DepNo

SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1)          -- 1
SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2)        -- 3
SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2,3)      -- 0
SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2,3,4)    -- 4
SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2,3,4,5)  -- 1

CHECKSUM_AGG 的參數值必須是整數，也就是只適用於整數型別的欄位，它的檢查法就是將所有欄位值以二進位互斥運算。

CHECKSUM_AGG VS CHECKSUM

• CHECKSUM_AGG 是彙總函式，它可以彙總多筆資料中的單一欄位做總和檢查碼計算。
• CHECKSUM 不是彙總函式，它可以針對一筆資料中的所有欄位做總和檢查碼計算。

--依據EmpNo,EmpName欄位，求每一筆資料的總和檢查碼
SELECT CHECKSUM(EmpNo,EmpName) FROM Emp

--依據所有欄位，求每一筆資料的總和檢查碼
SELECT CHECKSUM(*) FROM Emp 
         
--以下方法可用來比對二個 table 中的資料是否一樣   
SELECT CHECKSUM_agg(CHECKSUM(*)) FROM Emp 　

使用 Group By 子句

當使用彙總函式時，用來分組統計。

SELECT OrderID, 
    COUNT(1) as [COUNT], 
    SUM(UnitPrice) as [SUM], 
    AVG(UnitPrice) as [AVG], 
    MAX(UnitPrice) as [MAX], 
    MIN(UnitPrice) as [MIN]
FROM OrderDetails 
GROUP BY OrderID

COMPUTE 和 COMPUTE BY

• COMPUTE 和 COMPUTE BY 這二個子句必須與彙總函式搭配使用。
• COMPUTE 和 COMPUTE BY 子句可以針對特定欄位，再額外彙總一個結果。
• COMPUTE 和 COMPUTE BY 子句要放在整個查詢陳述式的後方。
• 可搭配的彙總函式包含 SUM、AVG、MIN、MAX 或 COUNT。

SELECT *
	FROM tblGrade
	COMPUTE SUM(Grade);

COMPUTE BY 則可以根據特定欄位值，進行群組彙總。

SELECT StudentNo,ClassID,Grade
	FROM tblGrade
	ORDER BY StudentNo, ClassID 
	COMPUTE SUM(Grade) BY StudentNo;

下一版的 Microsoft SQL Server 將不再提供此功能。請勿在新的開發工作中使用此功能，並且儘速修改使用此功能的應用程式。 改用 ROLLUP 。

使用 Having 子句

指定群組或彙總的搜尋條件。
HAVING 只能搭配 SELECT 陳述式使用。
HAVING 通常用在 GROUP BY 子句中。
當未使用 GROUP BY 時，HAVING 的行為會如同 WHERE 子句。

SELECT 
	CASE WHEN (GROUPING(TypeA)=1) THEN 'don''t care' ELSE TypeA END  as TYPEA,
	CASE WHEN (GROUPING(TypeB)=1) THEN 'don''t care' ELSE TypeB END  as TYPEB,
	CASE WHEN (GROUPING(TypeC)=1) THEN 'don''t care' ELSE TypeC END  as TYPEC,
	SUM(Qty) AS QtySum
FROM @List
GROUP BY TypeA , GROUPING SETS (TypeB,TypeC)
HAVING SUM(Qty)>10

• GROUPING SETS 對等項目