COMPUTE

SQL Server

Example

USE AdventureWorks2012;
GO
SELECT TerritoryID, SalesYTD
FROM Sales.SalesPerson
WHERE SalesYTD <> 0
ORDER BY TerritoryID;
COMPUTE SUM(SalesYTD), AVG(SalesYTD) BY TerritoryID;

Output

TerritoryID   SalesYTD
 ------------ -----------
 1             55890.6284
 1             55263.8106
 1             20307.5030
 ------------ -----------
 Compute Sum   131461.9420
 Compute Avg   43820.6473
 ------------ -----------
 2             53510.4916
 2             16221.8008
 ------------ -----------
 Compute Sum   69732.2924
 Compute Avg   34866.1462
 ------------ -----------

Explanation

The SELECT query retrieves and sorts data from the Sales.SalesPerson table where SalesYTD is not equal to 0. The results are listed according to TerritoryID.

Next, the COMPUTE clause calculates the sum and average of SalesYTD for each TerritoryID. The result set accordingly displays these computed values after each set of TerritoryID data.