10 new SQL sorting, grouping, and Statistics skills

Today, we can see a great deal on the new customer network. Article It is about SQL sorting, grouping, and Statistics skills! Let's share it with you! Haha

1. Sort data in order

Generally, all of your data really needs to be arranged in a certain order. The SQL order by statement can organize data in alphabetical or numerical order. Therefore, similar values are sorted by group. However, the sorting results of this grouping are not true grouping. Order by displays each record, and the group may represent many records.

2. Grouping to remove duplicate values

The biggest difference between sorting and grouping is that sorting data shows all records (within the specified range), while grouping data does not show all records. The group by statement only displays one record for the same value. For example, the group by statement in the following statement returns only the unique zip encoding column for repeated data in the data source.

Select zip from MERs group by zip

Only records defined by the Group by and select statements are included. In other words, the select list must meet the requirements
By list, but one exception is that the select list can contain aggregate functions (group by statements do not allow the use of Aggregate functions ). Note that group
The by statement does not sort result groups. In order to arrange groups in an orderly manner by letters or numbers, you must add an order by statement. In addition
By statements cannot reference fields that use aliases. Group Columns must be potential data, but they do not need to be displayed in the results.

3. filter data before grouping

You can add a where statement to filter data in the group by group. For example, the following statement returns only the unique zip encoding column of a customer in Kentucky.

Select zip from customerswherestate = 'ky' group by zip

Note that the where statement filters data before the value of the Group by statement. Like 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 the records you specified are displayed in the result group. Data that meets the Group definition but does not meet the filter conditions is not included in a group. When you want to score
When the Group contains all data, you can add the keyword "all". Then, the where condition does not work. For example, adding the keyword all in the previous example will return all zip groups, instead
Those in tacky.

Select zip from customerswherestate = 'ky' group by all zip

In this case, the two statements conflict, and you may not use the keyword all in this way. When you use an aggregate function to calculate a column, It is very convenient to use the all keyword. For example, the following statement calculates the number of customers in each Kentucky ZIP file and displays other zip values.

Select zip, count (ZIP) as kycustomersbyzip from Customerswherestate = 'ky 'group by all zip

The result group contains all the zip values in the potential data. However, for aggregated columns (kycustomersbyzip) that are not in the Kentucky zip group, 0 is displayed. Remote query does not support group by all.

5. filter data After grouping

Where statement in group
By statement. You can use the having statement to filter data After grouping. Generally, the returned results of the where statement and having statement are the same,
It is worth noting that these two statements are not interchangeable. When you are confused, follow the instructions below: Use the where statement to filter records and use the having statement to filter groups.

Generally, you use the having statement and an aggregate function to calculate a group. For example, the following statement returns a unique zip encoding column, but may not contain all the zip codes in the potential data source.

Select zip, count (ZIP) as customersbyzip from Customers group by zip having count (ZIP) = 1

Only groups containing one customer are displayed in the results.

6. Learn more about where and having statements

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

The where statement is prior to the group by statement; the SQL statement is calculated before the group.

Having statement after the group by statement; SQL calculates the having statement after the group.

7. Use Aggregate functions to count grouped data

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

Select orderid, sum (Cost * quantity) as ordertotal From orders group by orderid

For other groups, select and group by columns must match. This rule is an exception when a SELECT statement contains an aggregate function.

8. collect aggregated data

You can continue to display a category statistics for each group. The rollup operator of SQL can display an additional classification statistics for each group. This classification statistics is the result of using an aggregate function to calculate all records in each group. The following statement calculates ordertotal for each group:

Select customer, ordernumber, sum (Cost * quantity) As ordertotal from orders group by customer, Ordernumber with Rollup

For two groups whose values are 20 and 25 respectively, rollup displays an ordertotal value of 45. The first record in the rollup result is unique because it is used to calculate all group records. This value is the total value of the entire record set.

Rollup does not support distinct or group by all statements in aggregate functions.

9. Count each column

The cube operator goes further than rollup and returns the number of repeated values in each group. The result is the same as rollup, but each cube column of each customer contains an additional record. The following statement displays the statistics for each group and the statistics for each additional customer.

Select customer, ordernumber, sum (Cost * quantity) As ordertotal from orders group by customer, Ordernumber with cube

Cube provides the most comprehensive statistics. It not only performs aggregation and rollup, but also computes other columns that define the group. In other words, the cube counts each possible combination of columns.

Cube does not support the Group by all statement.

10: Sort statistical results

When the cube results are confusing (this is often the case), you can add a grouping function, as shown below:

Select grouping (customer), ordernumber, Sum (Cost * quantity) as ordertotal from orders Group By customer, ordernumber with cube

Each line in the result contains two additional values:

Value 1 indicates that the value on the left is a statistical value, which is a rollup or cube operator.

0 indicates that the value on the left is a detailed record generated by the initial group by statement.