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 (Analysis Services - Multidimensional Data).
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.
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.
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 AdventureWorks2008R2; GO 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 AdventureWorks2008R2; GO 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.
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.
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 AdventureWorks2008R2; GO SELECT SalesOrderID, SUM(UnitPrice), SUM(UnitPriceDiscount) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID;
You cannot include ntext, text, or image data types in a COMPUTE or COMPUTE BY clause.