Use of SQL COMPUTE by

The disadvantage of the GROUP by clause is that only the aggregated data is returned in the result set, without the original verbose record. If you want to do this work in SQL Server, you can use the COMPUTE BY clause. Compute generates totals as additional totals are listed at the end of the result set. When used with by, the COMPUTE clause generates control interrupts and subtotals within the result set.

The following SELECT statement uses a simple COMPUTE clause to generate a sum total of price and advance in the titles table:

Use pubs
SELECT type, price, advance
From titles
ORDER by Type
COMPUTE sum (price), sum (advance)

The following query adds an optional by keyword to the COMPUTE clause to generate 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 with 12 result sets, and each group in the six group has two result sets. The first result set for each group is a rowset that contains the information requested in the selection list. The second result set for each group contains a subtotal of the two SUM functions in the COMPUTE clause.

Rules for the COMPUTE BY clause:

(1) distinct cannot be used with the row statistics function

(2) compute??? In the By clause??? Column must appear in the select list

(3) You cannot use the SELECT INTO clause in a statement that contains a COMPUTE BY clause, because statements that include the COMPUTE clause produce irregular rows.

(4) If the COMPUTE BY clause is used, the ORDER BY clause must be used, and the columns in the COMPUTE BY clause must be included in the ORDER BY clause, and the sequence and start items of the column must be consistent (the column in the COMPUTE BY clause has to be the order All of the list in the By clause, or several successive steps in the front).

(5) If compute omits by, the order by can also be omitted

(6) If the COMPUTE BY clause contains more than one column, a group (the group with the first column) is divided into subgroups (using the following columns), and each level of subgroups is counted.

(7) When multiple COMPUTE by clauses are used, the results are counted separately by different groups. The details are displayed in the normal first grouping mode.

(8) Multiple statistical functions can be used in the COMPUTE BY clause, they do not affect each other

(9) COMPUTE BY clause can not include by, but only with compute at this time do not group the preceding information, but only the total information statistics.

Compare COMPUTE and GROUP by
The differences between COMPUTE and GROUP by are summarized as follows:
GROUP by generates a single result set. Each group has a row that contains only the grouping by column and the aggregate function that displays the group's sub-aggregations. The select list can only contain group by columns and aggregate functions.

COMPUTE generates multiple result sets. A class of result sets contains the detail rows for each group, which contains the expressions in the selection list. Another type of result set contains a child aggregation of a group, or a SELECT statement
Total aggregation of the. The select list can contain expressions other than the group by column or aggregate function. The aggregate function is specified in the COMPUTE clause, not in the selection list.
The following query uses the group by and aggregate functions; the query returns a result set with one row for each group that contains the aggregated subtotals for that group:
Use pubs
SELECT type, sum (price), sum (advance)
From titles
GROUP by Type

Description in the COMPUTE or COMPUTE by clause, the ntext, text, or image data type cannot be included.

Use of SQL COMPUTE by