The most commonly used methods of SQL sorting, grouping, and statistics

Organizing data in a meaningful way can be a challenge. Sometimes what you need may be a simple sort, but usually you need to do more and you need to group them for analysis and statistics. Fortunately, SQL provides a number of statements and actions to sort, group, and Digest. Some of the tips below will help you identify when to sort, when to group, when and how to count. For more information on each statement and action, see Books Online.

1. Use sorting to make the data orderly

In general, all your data really needs to be arranged in some order only. An ORDER BY statement for SQL can organize data in alphabetical or numeric order. Therefore, similar values are sorted by group. However, the results of this grouping are not really grouped. ORDER by displays each record and the grouping may represent many records.

2. Grouping to remove duplicate values

The biggest difference between sorting and grouping is that sorted data shows all records (within a qualifying range), while grouped data does not show all records. The GROUP by statement displays only one record for the same value. For example, the group BY statement in the following statement returns only a unique zip-encoded column for data that recurs in the data source.

Only those records that are defined collectively by the group BY and select statements, in other words, the select list must satisfy the group by list, but one exception is that the select list can contain aggregate functions (the GROUP by statement does not allow aggregation functions). It is important to note that the group BY statement does not sort the result groupings. In order for the grouping to be ordered alphabetically or numerically, you need to add an order by statement. In addition, you cannot reference a field that uses aliases in a group by statement. Grouped columns must be potential data, but they do not need to be displayed in the results.

3. Filtering data before grouping

You can add a where statement to filter the data in groups that have group by results. For example, the following statement returns only the unique zip-coded column for the Kentucky State customer.

It is important to note that the WHERE statement is filtered before the group BY statement is evaluated. As with the group by statement, the where statement does not support aggregate functions.

4. Return all Groups

When you use the Where statement to filter data, only those records that you specify are displayed in the result group, and data that conforms to the group definition but does not meet the filter criteria is not included in a grouping. When you want to include all the data in the grouping, add the keyword all, and the Where condition does not work. For example, adding the keyword in the previous example all would return all the zip groupings, not just those in Kentucky State.

In this case, there is a conflict between the two statements, and you may not use the keyword all in this way. When you use an aggregate function to calculate a column, it may be convenient to use the ALL keyword. For example, the following statement calculates the number of customers in each Kentucky State zip, and also displays additional zip values.

The result grouping includes all the zip values in the potential data, however, for those aggregation columns (kycustomersbyzip) that are not Kentucky State zip groupings, the 0 is displayed. The remote query does not support group by all.

5. Filter data after grouping

The WHERE statement is evaluated before the group by statement. When you need to filter the data after grouping, you can use the having statement. Typically, the where statement and the having statement return the same results, but it is worth noting that the two statements are not interchangeable. When you are confused, you can follow the instructions below: Use the Where statement to filter the records, and use the having statement to filter the groupings.

In general, you will use the having statement and an aggregate function to calculate a grouping. For example, the following statement returns a unique zip-coded column, but may not contain all of the zip in the potential data source.

Only those groups that contain one customer are displayed in the results.

6. Learn more about where and having statements

If you are still confused about when you should use where and when to use it, follow the instructions below:

• The WHERE statement precedes the group BY statement, and SQL calculates the where statement before grouping.
• Having a statement after the group BY statement; SQL calculates the having statement after grouping.

7. Grouping data by using aggregate function statistics

Grouping data can help us analyze the data, but sometimes we may need more information than just grouping. You can use aggregate functions to count grouped data. For example, the following statement shows the total price for each batch of purchase orders.

For other groupings, the Select and group by columns must match. This rule is an exception when the SELECT statement contains aggregate functions.

8. Statistical aggregation data

You can continue to statistic data for each grouping to display a categorical statistic. The rollup operator of SQL can display an additional categorical statistic for each grouping. This categorical statistic is the result of using aggregate functions to calculate all the records in each grouping. The following statement calculates OrderTotal for each grouping:

For groups that have two values of 20 and odertotal respectively, rollup displays a OrderTotal value of 45. The first record in the rollup result is unique because it calculates all the grouped records, which is the total value of the entire recordset.

Rollup does not support DISTINCT in aggregate functions, nor does it support GROUP by all statements.

9. Count each column

The cube operator is more than rollup, which returns the number of duplicate values in each group. It has the same results as rollup, but contains an extra record for each of the customer's column cubes. The following statement shows statistics for each grouping and additional per-customer statistics.

Cube can give the most comprehensive statistics. It not only accomplishes aggregation and rollup functions, but also calculates other columns that define groupings, in other words, cube counts each possible combination of columns.

Cube does not support the group by all statement. Yun Ding Casino

10. Sort the statistical results

When the result of the cube is confusing (it is often the case), you can add a grouping function as follows:

Each row in the result contains two additional values:

• A value of 1 means that the left value is a statistic and is an operator of the rollup or cube.
• A value of 0 means that the left-hand value is a detailed record that is generated by the original group BY statement.

The most commonly used methods of SQL sorting, grouping, and statistics