What is the usage of mysql aggregate function?

Mysql aggregate function usage: 1. Use the SELECT statement to return the total number of series values, the code is [SELECT SUM (quantity) AS total number]; 2. Use the AVG function to calculate the average, the code is [SELECT AVG (unit price) *Quantity) As Average Amount].

##More related free learning recommendations: mysql tutorial(Video)

mysql aggregate function usage:

1. SUM function:

Let's start with the SUM function. This function is usually used in a SELECT statement to return the total number of series values. Assume that the product project manager wants to know the total sales of the product so far, then we can use the following query script:

　　SELECT SUM(数量) AS 总数 
　　FROM ProductOrders

The execution statement will return the following results:

　　Total 
　　----------- 
　　1837

2, AVG The function (average function)

is used similarly to SUM, it gives us the arithmetic mean of the series values. This time we can try a slightly more complex task: find the average value of all orders in the North American continent. Note that we need to multiply the "Quantity" column and the "Unit Price" column to calculate the total amount of each order. The query script is as follows:

SELECT AVG(单价* 数量) As 平均金额 
FROM ProductOrders 
WHERE 所在地 = “北美洲”

The return result is as follows:

平均金额 
--------------------- 
862.3075

3. COUNT counting function

SQL provides the COUNT function to query if the set criteria are met The number of records. We can use the COUNT(*) syntax alone to retrieve the number of rows in a table. In addition, you can also use the WHERE clause to set counting conditions and return the number of specific records. For example, let's say our product sales manager wants to know how many orders for more than 100 products the company has processed. The following is a SQL query script that satisfies this condition:

SELECT COUNT(*) AS '大订单数量'
FROM ProductOrders
WHERE 数量> 100

The return result is as follows:

大订单数量 
---------------------- 
3

The COUNT function also allows the use of the DISTINCT keyword and expression to calculate the value that satisfies the expression in the target data The number of occurrences. Likewise, you can use the ALL keyword to return the total number of values ​​that satisfy an expression, regardless of whether there are duplicate values. For example, a product manager wants to return the number of "locations" in the database through a simple query.

First, let’s take a look at the query using the ALL keyword:

SELECT COUNT(ALL 所在地) As '所在地数量'
FROM ProductOrders

The returned result is:

所在地数量 
-------------------- 
7

Obviously this is not the result we need. Because according to the ProductOrders table, there are only three locations for all orders, namely North America, Africa, and Europe. Let's use the DISTINCT keyword instead:

SELECT COUNT(DISTINCT 所在地) As '所在地数量'
FROM ProductOrders

The returned result is:

所在地数量
-------------------- 
3

This is the result we want.

4. Maximum and minimum values

In the last section of this article, let’s take a look at what SQL provides us with to find the maximum value that satisfies a given expression. function of value and minimum value. The MAX() function returns the maximum value in a given data set. We can give the function a field name to return the maximum value of a given field in the table. You can also use expressions and GROUP BY clauses in the MAX() function to enhance the search function.

Still in the ProductOrders table, suppose our product manager wants to find the order that brings the most revenue to the company from this database. We can use the following query to find this order and return the total sales amount of the order:

　　SELECT MAX(数量 * 单价)As '最大的订单' 
　　FROM ProductOrders

The return result is as follows:

　　最大的订单 
　　--------------------- 
　　2517.58

The MIN() function is used similarly, but returns an expression the minimum value. Let's try a slightly more complex query using the MIN() function. Our sales department is currently analyzing data for small orders. They want to find out the minimum order for each location. In addition to calculating the value in the expression, this also requires the use of a GROUP BY clause to summarize the data at the location. The SQL query is as follows:

SELECT 所在地, MIN(数量 * 单价) AS '最小订单'
FROM ProductOrders 
GROUP BY 所在地

The returned result is as follows:

所在地       最小订单
------------- --------------------- 
非洲         167.04
欧洲        2099.02
北美洲    70.65

The above is the detailed content of What is the usage of mysql aggregate function?. For more information, please follow other related articles on the PHP Chinese website!