Chapter 2: Summary data, Chapter 2 Summary Data
Table Name: products
Field: product_id, product_name, product_price, vend_id (supplier) 12.1 clustering function:
We often need to summarize data instead of retrieving data. MySQL provides specialized functions.
Search example:
- Determine the number of rows in the table
- Obtain the sum of the row groups in the table
- Locate table columns
MySQL provides five Aggregate functions.
Aggregate Function: a function that runs on a row group to calculate and return a single value.
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 values of a column. |
12.1.1AVG () function:
Average price of all products
SELECT AVG(product_price) AS avg FROM products
Note: AVG () can only be used to determine the average value of a specific value column, and the column name must be given as a function parameter. To obtain the average value of multiple columns, you must use multiple AVG () functions.
12.1.2COUNT () function:
COUNT () can be used in two ways:
- COUNT (*) counts the number of all rows in the table.
- COUNT (column) counts the number of rows in a column.
Returns the number of all products:
SELECT COUNT(*) FROM products
Return the number of rows where the supplier is located:
SELECT COUNT(vend_id) FROM products
NOTE: If COUNT (column) is specified for a column, if the column has null, the row is ignored, but COUNT (*) does not.
MAX (): calculate the maximum value of a column (maximum value or date. If the last row is returned for text data, the null value row is automatically ignored ). MIN (): calculates the minimum value of a column (the minimum value and the minimum date. If the first row is returned for text data, the null value row is automatically ignored ). SUM (): calculates the SUM of all values in a column (null value rows are automatically ignored ).
12.2 clustering different values:
The preceding five functions can be used as follows:
- Calculate ALL rows and specify the ALL parameter (specified by default, that is, no need to specify)
- Specify the DISTINCT parameter for different values.
Find the average prices of all different prices
SELECT AVG(DISTINCT product_price) FROM products
Note: If the column name is specified, DISTINCT can only be used for COUNT ().
12.3 Aggregate functions:
Example:
SELECT AVG(product_price) AS avg,COUNT(product_id) AS count,MAX(product_price) AS max,MIN(product_price) AS min, SUM(product_price) AS sum FROM products