Explore the usage analysis of SQL COMPUTE by _mssql

A disadvantage of the GROUP by clause is that the result set returned has only aggregate data and no original detailed records. If you want to do this work in SQL Server, you can use the COMPUTE BY clause. The Compte build totals are listed as additional totals for the end of the result set. When used with by, the COMPUTE clause generates control interrupts and subtotals in the result set.

The following SELECT statement generates the sum total of price and advance in the titles table using a simple COMPUTE clause:

Copy Code code as follows:

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

Copy Code code as follows:

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 requested information in the select list. The second result set for each group contains subtotals for the two SUM functions in the COMPUTE clause.

COMPUTE BY clause rules:

(1) distinct can not be used with row statistic functions

(2) compute??? In BY clause??? Out columns must appear in the select list

(3) A SELECT INTO clause cannot be used in a statement that contains a COMPUTE BY clause because a statement that includes a COMPUTE clause produces an irregular row.

(4) If the COMPUTE BY clause is used, the ORDER BY clause must be used, and the column in the COMPUTE BY clause must be included in the orders by clause, and the column is consistent both before and after the start item (plainly compute the columns in the BY clause must be ordered All of the list in the By clause, or several consecutive preceding ones.

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

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

(7) When using multiple COMPUTE by clauses, the results are statistically divided by different groups. The details are displayed in the normal first group.

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

(9) The COMPUTE BY clause may not contain by, and only compute the preceding information at this time, and only the entire information is counted.

Compare COMPUTE and GROUP by
The difference between COMPUTE and GROUP by is summarized as follows:
GROUP by generates a single result set. Each group has a row that contains only the group by column and the aggregate function that displays the group's child aggregations. A select list can contain only grouped 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 select list. Another type of result set contains a child aggregation of a group, or a SELECT statement
The total aggregation. A select list can contain an expression other than the grouped by column or aggregate function. Aggregate functions are specified in the COMPUTE clause, not in the select 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 an aggregation subtotal for that group:
Use pubs
SELECT type, sum (price), sum (advance)
From titles
GROUP by Type

Description You cannot include ntext, text, or image data types in a COMPUTE or COMPUTE by clause .