Sqlserver-compute by

Original: Sqlserver-compute by

The COMPUTE by clause can view detail rows and view summary rows through the same SELECT statement. You can calculate the summary values for a subgroup, or you can calculate the total value of the entire result set

The COMPUTE clause requires the following information:

1. The optional by keyword allows you to calculate the specified row aggregation by one column

2. Row aggregation function: Sum,avg,min,max,count

3. Column on which the row aggregate function is to be executed

The summary value generated by COMPUTE is displayed as a detached result set in the query results, and the result of the query, including the COMPUTE clause, is similar to the control interrupt report, which is the report that summarizes the value that is controlled by the specified group. You can generate summary values for each group, or you can calculate multiple aggregate functions for the same group.

When compute has an optional by clause, each group that meets the select criteria has two result sets:

1. The first result set for each group is a detail rowset that contains the selection list information for that group

2. The second result set of each group has a row containing a small set of aggregate functions specified in the COMPUTE clause of the group.

Example:

Sql:

Select Sex,sclass,score

From student

ORDER BY sex

Compute SUM (score) by sex

Note: The order by is required, and the parameters after the compute by should appear in the parameters after the order by

When compute does not have an optional by clause, select has two result sets:

1. The first result set for each group is the information that contains all the detail rows of the selection list

2. The second result set has a row containing the aggregate of the aggregation functions specified in the COMPUTE clause

For example:

Sql:

Select Sex,sclass,score

From student

Compute SUM (Score)

Comparing compute and GROUP BY, the difference is as follows:

1. Group by generates a single result set, each with a row containing only the group by column and an aggregate function that displays the group's sub-aggregates, and the selection list can only contain group by columns and aggregate functions.

2. Compute generates multiple result sets, a class of result sets that contain the detail rows for each group, which contains the expressions in the selection list, and another type of result set that contains the child aggregations of the group.

3. The select list can contain an expression other than the Group by column or aggregate function, and the aggregate function is specified in the COMPUTE clause instead of appearing in the selection list.

Sqlserver-compute by