MySQL Data Collection and clustering functions (7)

MySQL data aggregation and clustering functions-MySQL series (7) 1. Clustering functions run on the row group to calculate and return a single function. SQL clustering function description AVG () returns the average COUNT () of a column returns the number of rows of a column MAX () returns the maximum MIN () of a column returns the minimum SUM () of a column () returns the sum of a column (1), AV

MySQL data aggregation and clustering functions-MySQL series (7) 1. Clustering functions run on the row group to calculate and return a single function. SQL clustering function description AVG () returns the average COUNT () of a column returns the number of rows of a column MAX () returns the maximum MIN () of a column returns the minimum SUM () of a column () returns the sum of a column (1), AV

MySQL data collection and aggregation functions-MySQL series (7)
1. Aggregate functions

Aggregate functions are functions that run on a row group to calculate and return a single value.

SQL Aggregate functions

Function	Description
AVG ()	Returns the average value of a column.
COUNT ()	Returns the number of rows in a column.
MAX ()	Returns the maximum value of a column.
MIN ()	Returns the minimum value of a column.
SUM ()	Returns the sum of a column.

(1) AVG () function can return the average value of all columns or the average value of a specific column.

SELECT AVG(prd_price) AS avg_price FROM productsWhere vend_id=1003;

Products with vend_id = 1003 are filtered out. avg is the average value of these products. (2) COUNT () the COUNT (*) function counts the number of rows in the table, regardless of whether the column contains NULL or non-NULL values; COUNT (column) calculate the rows with values in a specific column and ignore NULL values.
(3) The MAX () function MAX () returns the maximum value of the specified column. You must specify the column name and ignore the NULL value. In MySQL, the MAX () function can be used for non-data columns. When used for text data, if the data is sorted by the corresponding column, MAX () returns the last row. (4). the MIN () function MIN () returns the minimum value of the specified column. You must specify the column name and ignore the NULL value.
In MySQL, the MIN () function can be used for non-data columns. When used for text data, if the data is sorted by the corresponding column, MIN () returns the first row.
(5) and SUM () functions are used to return the SUM (total) of the specified column and ignore NULL rows.

Select sum (num) AS prod_sum --- return the number of items in the specified order number FROM productsWhere order_id = 123456;

SUM () can also calculate the total value.

Select sum (item_price * num) AS totol_price FROM order_items -- return the prices and values of all commodities in the order. Where order_id = 123456;

2. The following SQL statement aggregates the DISTINCT Keywords of different values to return the average value of different price items with vend_id = 1003

SELECT AVG(DISTINCT prd_price) AS avg_price FROM productsWhere vend_id=1003;

3. The aggregate function can be used in combination.

SELECT count(*) AS num,            MIN(prod_price) AS price_min,            MAX(prod_price) AS price_max,            AVG(prod_price) AS price_avgFROM products;