Statistics and use of query results to create a new table

In a SELECT statement, you can use aggregate functions, rowset functions, GROUP by clauses, and COMPUTE clauses to count query results. A GROUP by clause can be used with a rowset function or aggregate function, whereas a COMPUTE clause can only be used with a rowset function.
In a SELECT statement, you can also simply use aggregate functions for statistics, when it puts all the eligible data statistics together to form a row of statistical data, this statistical method is called scalar statistics.
Example: Statistics on the average price of "business" books
SELECT ' Average price ' =avg (price)
From titles
WHERE type= ' Business '
1.GROUP BY clause
The syntax format of the GROUP by clause is:
GROUP by [all] aggregate_free_expression
[Maggregate_free_expression] ...
Example: to count the average price of each category of books
SELECT Type,avg (price), ' average price '
From titles
GROUP by Type
2.HAVING clause
When using the GROUP BY clause, you can also use the HAVING clause to further set statistical conditions for grouping statistics, having clauses that are related to the GROUP BY clause and the WHERE clause to the SELECT clause.
A HAVING clause can refer to any of the items in the select list, and you can also use logical operators to concatenate multiple conditions, up to 128, in the HAVING clause.
To count the average price of a book other than a specified type by category of books

You can use aggregate functions in the HAVING clause.
Example: The average price of a book is grouped by book category, but excluding only one book category

Statistical results sorted:

Example: The average price is calculated by book category and sorted by average price

The all option in the 3.GROUP by clause
When you use the group BY and WHERE clauses for grouping statistics in a SELECT statement, only the data items that meet the criteria defined in the WHERE clause are listed in the statistics list.
If the all option is used in a GROUP BY clause, all groupings in the statistics list are listed, even those groupings that do not conform to the conditions specified in the WHERE clause are listed but are not counted.
Cases:

The results listed business books, but it does not mean that the average price of business books is empty, but that such books do not meet the statistical conditions.
Matters to be noted with the GROUP BY clause:
(1) You cannot use aggregate functions in the GROUP BY clause.
(2) All the nonclustered items in the Select select list must be listed in the GROUP BY clause.
Example: The type,pub_id column must be listed in the GROUP BY clause of the following statement, integral

4.COMPUTE clause
Not only does it display statistical results, but it also displays the details of the statistics.
The syntax format for the COMPUTE clause is:

Use the by option in the COMPUTE clause to group statistics on the data.
When you use the COMPUTE clause, you must observe the following rules:
(1) The DISTINCT keyword cannot be used in aggregate functions.
(2) The COMPUTE by clause must be used in conjunction with an ORDER BY clause, and the list of column names in the COMPUTE by clause must be the same in the ORDER BY clause, or a subset of them, and must be addressed from left to right.
(3) When the by option is not used in the COMPUTE clause, the aggregated value is counted.

Second, the use of query results to create a new table

Use the INTO option in the SELECT statement to write query results into a new table structure that is the same as the field structure in the select list.
Cases:

SELECT * into #new_publishers
From publishers
From publishers
WHERE country= ' USA '