| Function |
Description |
| AVG () |
Returns the average 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 value |
1, AVG () function, you can return the average of all columns, or you can return the average of a particular column or row. Example: SELECT avg (prod_price) as Avg_price alias from product; Description: Returns the average price for all products in the table. select avg (prod_price) as Avg_price alias from product wher ven_id=1003; Description: Returns the average price for products provided by a specific vendor. NOTE: Only the single column AVG function can only be used to determine the average of a particular numeric column, and the column name must be given as a function. In order to get the average of multiple columns, you must use multiple AVG function 2, COUNT (), count () functions, which can be used to determine the number of rows in a table or the number of lines that match a particular condition. There are two ways to use: A, count the number of rows in a table using count (*), regardless of whether the table column contains a null value or a non-null value B, use COUNT (Colum) to count the rows with values in a particular column, ignoring null values. Example: SELECT count (*) as num_cust from customers; Description: Returns the total number of customers in the Customers table select COUNT (cust_email) as Num_cust from Customers; Description: Only the customer count, 3, Max () function with e-mail address, maximum value, is required to specify the column name. Example: The SELECT MAX (Prod_price) as Max_price from Products; 4, min () function returns the minimum value of the specified column, which requires specifying the column name. Example: the Select MIN (prod_price) as Min_price from Products; 5, SUM () function returns the sum (total) example of the specified column value: Select SUM (quantity) as Items_ Ordered from OrderItems WHERE order_num=20005; Description: Retrieves the total number of items ordered (all quantity sum) sum () functions can also be used to aggregate calculated values. Example: SELECT SUM (item_price*quantity) as Total_price from OrderItems WHERE order_num=20005; 6, DISTINCT () aggregation different values are used to contain onlyA different value. Example: SELECT AVG (DISTINCT prod_price) as Avg_price from the products WHERE ven_id=1003; Description: This average takes into account only the different prices note: If you specify a column name, the DISTINCT Can only be used with count (). DISTINCT cannot be used with count (*), so count (DISTINCT) is not allowed, otherwise an error is generated. Similarly, distinct must specify the column name and cannot be used for calculations or expressions. 7, combined aggregation functions. The SELECT statement can contain multiple aggregate functions as needed, examples: Select COUNT (*) as Num_items,min (Prod_price) as Price_min,max (Prod_price) as Price_max, AVG (Prod_price) as Price_avg,from products;
MYSQL Learning Notes Collation One of the aggregation functions
