Summarizing Data Using COMPUTE and COMPUTE BY
The COMPUTE and COMPUTE BY clauses are provided for backward compatibility. Instead, use these components:
- Microsoft® SQL Server™ 2000 Analysis Services in conjunction with OLE DB for Analysis Services or Microsoft ActiveX® Data Objects Multidimensional (ADO MD). For more information, see Microsoft SQL Server™ 2000 Analysis Services.
- 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 entire result set.
The COMPUTE clause takes the following information:
- The optional BY keyword, which calculates the specified row aggregate on a per column basis.
- A row aggregate function name; for example, SUM, AVG, MIN, MAX, or COUNT.
- A column to perform the row aggregate function upon.
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 includes a COMPUTE clause are like a control-break report, which 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 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 details rows containing the select list information for that group.
- The second result set for each group has one row containing 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 of the detail rows containing the select list information.
- The second result set has one row containing the totals of the aggregate functions specified in the COMPUTE clause.
Examples Using COMPUTE
This SELECT statement uses a simple COMPUTE clause to produce a grand total of the sum of the price and advances from the titles table:
USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)
This query adds the optional BY keyword to the COMPUTE clause to produce subtotals for each group:
USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
The results of this SELECT statement are returned in 12 result sets, 2 result sets for each of the 6 groups. The first result set for each group has a set of rows containing the information called for in the select list. The second result set for each group contains the subtotals of the two SUM functions in the COMPUTE clause.
Note Some utilities, such as osql, display multiple subtotal or total aggregate summaries in a way that may lead users to assume that each subtotal is a separate row in a result set. This is due to how the utility formats the output; the subtotal or total aggregates are returned in one row. Other applications, such as SQL Query Analyzer, format multiple aggregates on the same line.
Comparing COMPUTE to GROUP BY
To summarize 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 showing the subaggregate for that group. The select list can contain only the grouping columns and aggregate functions.
- COMPUTE produces multiple result sets. One type 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.
This query uses GROUP BY and aggregate functions; it returns one result set having one row per group containing the aggregate subtotals for that group:
USE pubs
SELECT type, SUM(price), SUM(advance)
FROM titles
GROUP BY type
Note You cannot include ntext, text, or image data types in a COMPUTE or COMPUTE BY clause.