MySQL data aggregation and aggregation functions-MySQL series (7) 1. aggregation functions
Aggregate functions are functions that run on a row Group to calculate and return a single value.
SQL aggregate functionsFunctionDescriptionAVG () 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) and AVG (). you can return the average values of all columns or the average values of specific columns.
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;