COMPUTE

COMPUTE is an SQL command used to generate report-like results with subtotals and totals in the query result set. It groups the result set and applies aggregate functions, providing calculations of numeric column values within the set.

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.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.