8. Aggregation Function---SQL

One, AVG () function

A VG () calculates the average of the column by counting the rows in the table and calculating the sum of its column values. A VG () can be used to return the average of all columns, or it can be used to return the average of a particular column or row.

Warning: For a single column only
AVG () can only be used to determine the average value of a particular numeric column, and the column name must be given as a function parameter. To get the average of multiple columns, you must use multiple a VG () functions.
Description: null value
The A VG () function ignores rows where the column value is null.

Second, COUNT () function

The count () function is counted. Count () can be used to determine the number of rows in a table or the number of lines that meet a specific condition.
The COUNT () function is used in two ways:
Use COUNT (*) to count the number of rows in a table, regardless of whether the table column contains a null value (NULL) or a non-null value.
Use Count (column) to count the rows that have values in a particular column, ignoring null values.

SELECT COUNT (* as num_cust from Customers;

Output Num_cust -- ------ 5

Analysis

In this example, counting all rows with count (*), regardless of the value of the columns in the row

SELECT COUNT  as Num_cust  from Customers;

Output Num_cust -- ------ 3

Analysis
This SELECT statement uses count (Cust_email) to count the rows that have values in the Cust_email column. In this example, the count of Cust_email is 3 (representing only 3 of the 5 customers
Email address).

Description: null value
If you specify a column name, the count () function ignores rows that have a null value for the specified column, but does not ignore if the count () function uses an asterisk (*).

Three, MAX () functions

MA X () returns the maximum value in the specified column. MA X () requires specifying the column name,

Tip: Use Max () for non-numeric data
Although Ma X () is typically used to find the largest numeric or date value, many (not all) DBMS allow it to return the maximum value in any column, including the maximum in the returned text column
Value. When used with text data, MA X () returns the last row sorted by that column.
Description: null value
The MA X () function ignores rows where the column value is null.

Four, MIN () function

The function of MIN () is exactly the opposite of the MA X () function, which returns the minimum value of the specified column. As with Ma X (),

Tip: Use min () for non-numeric data
Although min () is generally used to find the smallest numeric or date value, many (not all) DBMS allow it to return the smallest value in any column, including the smallest in the returned text column
Value. When used with text data, MIN () returns the first row after the column is sorted.
　
Description: null value
The MIN () function ignores rows where the column value is null.

V. SUM () function

SUM () returns the sum (grand total) of the specified column value.

Tip: Perform calculations on multiple columns
With standard arithmetic operators, all aggregation functions can be used to perform computations on multiple columns.
　
Description: null value
The SUM () function ignores rows where the column value is null.

Vi. Aggregation of different values

The above 5 aggregation functions can be used as follows:

• Performs a calculation on all rows, specifying a ll parameter or not specifying a parameter (because a ll is the default behavior).
• Contains only a different value, specifying the DISTINCT parameter.

Tip: All is the default
A ll parameter does not need to be specified because it is the default behavior. If you do not specify distinct, it is assumed to be a LL.
Description: Do not use in Access
Microsoft Access does not support distinct in aggregation functions, so the following example is not suitable for access. To get similar results in Access, you need to use a subquery
Returns the distinct data to the external select COUNT (*) statement.

SELECT AVG (DISTINCTas  avg_price  from ProductsWHERE='  DLL01';

Analysis
When distinct is used, the results may vary. Excludes them from repeated values or decreases the average.

Warning: Distinct cannot be used for count (*)
If you specify a column name, distinct can only be used for count (). Distinct cannot be used for count (*). Similarly, distinct must use column names and cannot be used to calculate or express
Expression
Tip: Use distinct for min () and Max ()
Although distinct is technically available for min () and Ma X (), this does not actually have value. The minimum and maximum values in a column, regardless of whether or not only different values are considered, result in a phase
The same.
Description: Additional aggregation parameters
In addition to the distinct and a ll parameters described here, some DBMS supports other parameters, such as top and top PERCENT, which support the calculation of subsets of query results. To understand the
Which parameters are supported by the body's DBMS, refer to the appropriate documentation.

Vii. Combinatorial aggregation functions

SELECT COUNT (*  as Num_items,MIN as price_min,MAX as Price_max, AVG  as Price_avg  from Products;

Analysis
Here, 4 aggregation calculations are performed with a single SELECT statement, returning 4 values (the number of items in the Products table, the highest value of the product price, the lowest value, and the average).
Warning: taking aliases
When you specify an alias to contain the results of a clustered function, the actual column names in the table should not be used. While this is legal, many SQL implementations do not support it and may produce ambiguous
The error message.

8. Aggregation Function---SQL