Summarizing Data Using COMPUTE and COMPUTE BY

The COMPUTE and COMPUTE BY clauses are provided for backward compatibility. Instead, use these components:

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:

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:

When COMPUTE is specified without the optional BY clause, there are two result sets for the SELECT:

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 Server Query Analyzer, format multiple aggregates on the same line.


Comparing COMPUTE to GROUP BY

To summarize the differences between COMPUTE and GROUP BY:

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.


See Also
Query Fundamentals SELECT


(c) 1988-98 Microsoft Corporation. All Rights Reserved.