Summarizing Data Using COMPUTE and COMPUTE BY
The COMPUTE and COMPUTE BY clauses are provided for backward compatibility. Instead, use the following components:
- SQL Server 2005 Analysis Services (SSAS) with OLE DB for Analysis Services or Microsoft ActiveX Data Objects Multidimensional (ADO MD). For more information, see Analysis Services Data Access Interfaces (SSAS).
- The ROLLUP operator. For more information, see Summarizing Data Using ROLLUP.
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.
The COMPUTE clause takes the following information:
- The optional BY keyword. This calculates the specified row aggregate on a per column basis.
- A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
- A column upon which to perform the row aggregate function.
Results Sets Generated by COMPUTE
The summary values generated by COMPUTE appear as separate result sets in the query results. The results of a query that include a COMPUTE clause are like a control-break report. This is a report whose summary values are controlled by the groupings, or breaks, that you specify. You can produce summary values for groups, and you can also calculate more than one aggregate function for the same group.
When COMPUTE is specified with the optional BY clause, there are two result sets for each group that qualifies for the SELECT:
- The first result set for each group has the set of detail rows that contain the select list information for that group.
- The second result set for each group has one row that contains the subtotals of the aggregate functions specified in the COMPUTE clause for that group.
When COMPUTE is specified without the optional BY clause, there are two result sets for the SELECT:
- The first result set for each group has all the detail rows that contain the select list information.
- The second result set has one row that contains the totals of the aggregate functions specified in the COMPUTE clause.
Examples That Use COMPUTE
The following SELECT statement uses a simple COMPUTE clause to produce the grand total of the unit prices and discounts from the SalesOrderDetail table:
USE AdventureWorks
SELECT SalesOrderID, UnitPrice, UnitPriceDiscount
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID
COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount)
The following query adds the optional BY keyword to the COMPUTE clause to produce subtotals for each sales order:
USE AdventureWorks
SELECT SalesOrderID, UnitPrice, UnitPriceDiscount
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID
COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount) BY SalesOrderID
The results of this SELECT statement return two result sets for each sales order. The first result set for each sales order has a set of rows that contains the information specified in the select list. The second result set for each sales order contains the subtotals of the two SUM functions in the COMPUTE clause.
Note
In some utilities, such as osql, the multiple subtotal or total aggregate summaries appear as if each subtotal is a separate row in a result set. This is because of how the utility formats the output; the subtotal or total aggregates are returned in one row. Other applications, such as SQL Server Management Studio, format multiple aggregates on the same line.
Comparing COMPUTE to GROUP BY
The following summarizes the differences between COMPUTE and GROUP BY:
- GROUP BY produces a single result set. There is one row for each group containing only the grouping columns and aggregate functions that show the subaggregate for that group. The select list can contain only the grouping columns and aggregate functions.
- COMPUTE produces multiple result sets. One kind of result set contains the detail rows for each group containing the expressions from the select list. The other type of result set contains the subaggregate for a group, or the total aggregate for the SELECT statement. The select list can contain expressions other than the grouping columns or aggregate functions. The aggregate functions are specified in the COMPUTE clause, not in the select list.
The following query uses GROUP BY and aggregate functions. The query returns one result set that has one row per group containing the aggregate subtotals for that group:
USE AdventureWorks
SELECT SalesOrderID, SUM(UnitPrice), SUM(UnitPriceDiscount)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Note
You cannot include ntext, text, or image data types in a COMPUTE or COMPUTE BY clause.