SQL Server Group by and Compute by

First, GROUP by

Rule 1: Single-value rules, followed by the list after select, must be returned and only one value returned for each grouping.

The field specified in select is either to be included in the group by statement, as a basis for grouping, or to be included in an aggregate function.

Therefore, we would like to find out the name, department, and salary of the person with the highest wage for each department. We need to find another solution.

　　Solution 1: Associate Subqueries

Select Name, department, payroll from Salary table as T1where not EXISTS (SELECT NULL from salary table as T2 WHERE T1. Department = T2. Department and T2. Wages > T1. Wages)

The output is as follows:

　　

Fully meet the requirements. For the associated subquery above, it can be understood as:

Traverse all records of the payroll table to find records that do not have the same salary as the current record department.

　　Although the syntax for correlating subqueries is simple, performance is not good. Because the subquery is executed once for each record.

Solution 2: Derived tables

The idea of using a derived table is to execute a subquery first, get a temporary result set, and then use the temporary result set and the original table for the inner JOIN operation. Information about the person who can get the highest wage.

Select name, T1. Department, payroll from Salary table as T1 INNER JOIN (    SELECT Department, MAX (payroll) as highest from payroll    --Execute query, first record two fields department-Max wage    GROUP by Department) as T2        --derivative table t2on T1. Department = T2. Department and wages = highest

　　

The way the derived table performs better than the associated subquery, because the way the derived table executes only one subquery at a time. But it needs a temporary table to store the temporary records. Therefore, this solution is not the best solution.

　　Solution 3: Use Join + is NULL

This is a better solution, when we use an outer join to match the record, when the matching record does not exist, it will replace the corresponding column with NULL.

Let's start by looking at a very simple SQL statement:

　　

What do you see from it? Null is returned when there is no record in the T2 table that is higher than the salary in the T1 table.

So, then, is it null to solve the problem?

　　

Good, wonderful method, let a person simply astounding use outer JOIN.

The join solution works for large data queries and scalable comparisons. It is always better suited to variable data volumes than a subquery-based solution.

　　Solution 4: Use aggregate functions for additional columns

We know that when GROUP by, the select list must return a single value, can we use an aggregate function to get this column to return a single value? The answer is yes.

　　

In fact, the returned data is problematic, and when the salary is the same, it returns the first name arranged by name from the big to the small. In other words, when the salary is the same, it can only return a record.

Let's change the aggregation function to a min look.

　　

　　Solution 5:row_number () + Over

With B as (    SELECT row_number () Up (PARTITION by Name ORDER by Createtime) as part, score, Name, Createtime    from XXX) SELECT * from B WHERE part = 1

The output is as follows:

　　

Ii. Compute and 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. Compte 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.

SELECT * from A where quantity > 8

Execution Result:

Example 10:compute

Select *from awhere quantity >8compute max (quantity), min (quantity), Avg (qty)

The results of the implementation are as follows:

The COMPUTE clause is able to observe the data details of the query results or to count the column data (such as Max, Min, and Avg in Example 10), and the returned results are composed of the select list and the compute statistic results.

Example 11:compute by

Select *from Awhere quantity >8order by category compute MAX (quantity), min (quantity), AVG (quantity) by category

The results of the implementation are as follows:

Example 11 compares the "Order by category" and the "... by category" as compared to example 10, and the results of example 10 are actually displayed in groups (A, B, c), with each group composed of the reorganization data list and the reorganization statistics results, plus:

• The COMPUTE clause must be used with the ORDER BY clause
• Compute...by GROUP by can only get statistical results for each group of data, rather than

The role of compute and compute by is not very large in real-world development, SQL Server supports compute and compute by, and access does not support

SQL Server Group by and Compute by