SQL must-know note the Nineth chapter summarizes the data

9.1 Aggregation Functions

Aggregation functions (aggregate function): A function that runs on a row group, evaluates and returns a single value.

9.1.1AVG () function

AVG () calculates the average of the column by counting the number of rows in the table and calculating the sum of the specified column values.

SELECT AVG(prod_price) AS avg_priceFROM ProductsWHERE vend_id = ‘DLL01‘;

Only for single columns: AVG () can only be used to determine the average of a particular numeric column, and the column name must be given as a function parameter, in order to get the average of multiple columns, you must use multiple AVG () functions.
Null value: The AVG () function ignores rows where the column value is null.

9.1.2COUNT () function

The count () function is counted. Count () can be used to determine the number of rows in a table or to match a specific condition.
Returns the total number of customers in the Customers table

SELECT COUNT(*) AS num_custFROM Custmers;

Count customers with e-mail addresses:

SELECT COUNT(cust_email) AS num_custFROM Customers;

Null value: If you specify a column name, rows with a null value for the specified column are ignored by the count () function, but are not ignored if the count () function uses an asterisk (*).

9.1.3MAX () function

Max () returns the maximum value for the specified column type. MAX () requires that the column name be specified.

SELECT MAX(prod_price) AS max_priceFROM Products;

Use Max () for non-numeric data: When used with text data, Max () returns the last row if the data is sorted by the appropriate column.
Null value: The MAX () function ignores rows where the column value is null.

9.1.4MIN () function

MIN () functions just as opposed to the max () function, which returns the minimum value of the specified column. As with Max (), MIN () requires that the column name be specified.

SELECT MIN(prod_price) AS min_priceFROM Products;

A null value: the MIN () function ignores rows where the column value is null.

9.1.5SUM () function

SUM () is used to return the sum of the specified column values.
Retrieves the total number of items ordered.

SELECT SUM(quantity) AS items_orderedFROM OrderItemsWHERE order_num = 20005;

SUM () can also be used to sum the calculated value and calculate the total order

SELECT SUM(item*quantity) AS total_priceFROM OrderItemsWHERE order_num = 20005;

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

9.2 Aggregating different values

All is the default: the all parameter does not need to be specified because it is the default behavior and if distinct is not specified, all is assumed.
The distinct parameter is used, so the average value only takes into account the various prices.

SELECT AVG(DISTINCT prod_price) AS avg_priceFROM ProductsWHERE vend_id = ‘DLL01‘;

9.3 Combined Aggregation functions

SELECT COUNT(*) AS num_items,          MIN(prod_price) AS price_min,          MAX(prod_price) AS price_max,          AVG(prod_prce) AS price_avgFROM Products;

Alias: When a result of a clustered function is included outside the specified alias, the actual column names in the table should not be used, although it is not illegal to do so, but many SQL implementations do not support and may produce ambiguous error messages.

SQL must-know note the Nineth chapter summarizes the data