Shulou(Shulou.com)06/01 Report--

How to use the mysql aggregate function? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

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

Mysql aggregate function usage:

1. SUM function:

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

SELECT SUM (quantity) Total AS FROM ProductOrders

Executing the statement returns the following result:

Total-1837

2. AVG function (average function)

The method of use is similar to SUM, which provides us with the arithmetic mean of a series of values. This time we can try a slightly more complicated task: to find out the average amount of all orders in North America. Note that we need to multiply the quantity column and the unit price column to calculate the total amount for each order. The query script is as follows:

SELECT AVG (unit price * quantity) As average amount FROM ProductOrders WHERE location = "North America"

The returned result is as follows:

Average amount-862.3075

3. COUNT counting function

SQL provides a COUNT function to query the number of records that meet the set criteria. We can use a separate COUNT (*) syntax to retrieve the number of rows in a table. In addition, you can use the WHERE clause to set the counting condition and return the number of entries for a particular record. For example, suppose our product sales manager wants to know how many orders the company has processed for more than 100 products. Here is the SQL query script that meets this condition:

SELECT COUNT (*) AS 'large order quantity' FROM ProductOrdersWHERE quantity > 100

The returned result is as follows:

Large order quantity-3

The COUNT function also allows you to use the DISTINCT keyword and expression to calculate the number of values that satisfy the expression in the target data. Similarly, you can use the ALL keyword to return the full number of values that satisfy the expression, whether or not there are duplicate values. For example, the product manager wants to return the number of "locations" in the database through a simple query.

First, let's look at the query that uses the ALL keyword:

SELECT COUNT (ALL location) As' number of seats' FROM ProductOrders

The returned result is:

Number of seats-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 location) As' number of seats' FROM ProductOrders

The returned result is:

Number of seats-3

This is what we want.

4. Maximum and minimum

In the last section of this article, let's take a look at the functions that SQL provides to find the maximum and minimum values that satisfy a given expression. The MAX () function returns the maximum value in a given dataset. 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 lookup.

Or 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 (quantity * unit price) As' largest order 'FROM ProductOrders

The returned result is as follows:

Largest order-2517.58

The use of the MIN () function is similar, but returns the minimum value of the expression. Let's try a slightly more complex query with the MIN () function. Our sales department is currently analyzing the data of small orders. They want to check the minimum order for each location. In addition to calculating the value in the expression, you need to use the GROUP BY clause to summarize the local data. The SQL query is as follows:

SELECT location, MIN (quantity * unit price) AS 'minimum order' FROM ProductOrders GROUP BY location

The returned result is as follows:

Location minimum order-Africa 167.04 Europe 2099.02 North America 70.65 Thank you for reading! After reading the above, do you have a general idea of how to use the mysql aggregate function? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.