AdventureWorks2012 範例資料庫:`Sales.SalesOrderDetail` (明細) + `Sales.SalesOrderHeader` (主單)。
SELECT CarrierTrackingNumber,
SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail
GROUP BY CarrierTrackingNumber;
SELECT D.ProductID, H.OrderDate, SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY D.ProductID, H.OrderDate
ORDER BY SummarizedLineTotal DESC;
SQL Server 視 **所有 NULL 為相等**,歸為同一群組。
CREATE TABLE #NullGroupBy (OrderDate DATE, Amount INT);
INSERT #NullGroupBy VALUES (NULL,100), ('2014-10-30',100), ('2014-10-31',100), (NULL,100);
SELECT OrderDate, SUM(Amount) AS TotalAmount
FROM #NullGroupBy
GROUP BY OrderDate;
/* 結果:
OrderDate TotalAmount
NULL 200
2014-10-30 100
2014-10-31 100
*/
DROP TABLE #NullGroupBy;
可對欄位套用函數/運算式後再分組,如按 **年月** 彙總:
SELECT CONVERT(CHAR(7), H.OrderDate, 120) AS [YYYY-MM],
SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY CONVERT(CHAR(7), H.OrderDate, 120)
ORDER BY SummarizedLineTotal DESC;
SELECT D.ProductID, H.OrderDate, SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY D.ProductID, H.OrderDate
HAVING SUM(LineTotal) > 200000
ORDER BY SummarizedLineTotal DESC;
-- 多層次小計一次查詢
SELECT ProductID, OrderDate, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY GROUPING SETS (
(ProductID, OrderDate), -- 明細
(ProductID), -- 產品小計
(OrderDate), -- 日期小計
() -- 總計
);
`GROUPING_ID()` 函數可識別各列屬於哪個分組集。