Chapter 2: Summary data, Chapter 2 Summary Data

Source: Internet
Author: User

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

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.