Shulou(Shulou.com)05/31 Report--

This article introduces you how to discuss the use of SQL compute by analysis, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The drawback of the GROUP BY clause is that there is only aggregate data in the returned result set, not the original detailed record. If you want to do this in SQL SERVER, you can use the COMPUTE BY clause. The COMPTE generated totals are listed at the end of the current result set as an additional summary. When used with BY, the COMPUTE clause generates control interrupts and categorized summaries in the result set.

The following SELECT statement uses a simple COMPUTE clause to generate the sum of price and advance in the titles table: the copy code is as follows: USE pubsSELECT type, price, advanceFROM titlesORDER BY typeCOMPUTE SUM (price), SUM (advance)

The following query adds an optional BY keyword to the COMPUTE clause to generate a subtotal for each group:

The USE pubs copy code is as follows: SELECT type, price, advanceFROM titlesORDER BY typeCOMPUTE SUM (price), SUM (advance) BY type

The results of this SELECT statement are returned with 12 result sets, with two result sets for each of the six groups. The first result set of each group is a rowset that contains the information requested in the selection list. The second result set of each group contains a subtotal of the two SUM functions in the COMPUTE clause.

Rules for the compute by clause:

(1) you cannot use distinct with row statistics functions.

(2) compute In the by clause? The outgoing column must appear in the selection list

(3) select into clauses cannot be used in statements that contain compute by clauses, because statements that include compute clauses produce irregular rows.

(4) if you use the compute by clause, you must use the order by clause, and the columns in the compute by clause must be included in the order by clause, and the order and starting items of the columns must be the same (to put it bluntly, the columns in the compute by clause must be all of the list in the order by clause, or several consecutive columns in front of them).

(5) if by is omitted from compute, order by can also be omitted.

(6) if the compute by clause contains multiple columns, a group (the group of the first column) will be divided into several subgroups (using the following columns), and each layer of subgroups will be counted.

(7) when multiple compute by clauses are used, the results will be calculated according to different groups. The details are still displayed in the normal first grouping mode.

(8) multiple statistical functions can be used in the compute by clause, and they do not affect each other.

(9) by can not be included in the compute by clause, but only compute can be used to not group the previous information at this time, but only count all the information.

The differences between COMPUTE and GROUP BY 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 subaggregation of the group. The selection list can only contain grouping by columns and aggregate functions.

COMPUTE generates multiple result sets. A result set contains detail rows for each group, which contains expressions in the selection list. The other type of result set contains subaggregations of groups, or total aggregations of SELECT statements. The selection list can contain expressions other than grouping by column or aggregate function. Aggregate functions are specified in the COMPUTE clause, not in the select list. The following query uses GROUP BY and aggregate functions; the query returns a result set with a row for each group that contains the aggregate subtotal of the group: USE pubs SELECT type, SUM (price), SUM (advance) FROM titles GROUP BY type indicates that the ntext, text, or image data types cannot be included in the COMPUTE or COMPUTE BY clause.

On how to explore the use of SQL compute by analysis to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.