MySQL 聚合函数

Summary: in this tutorial, you will learn how to use the MySQL aggregate functions including AVG, COUNT, SUM, MAXand MIN.

Introduction to MySQL aggregate functions

The data that you need is not always stored in the tables. However, you can get it by performing the calculations of the stored data when you select it. For example, you cannot get the total amount of each order by simply querying from the orderdetails table because the orderdetails table stores only quantity and price of each item. You have to select the quantity and price of item for each order and calculate the order’s total. To perform such calculations in a query, you use aggregate functions.

By definition, an aggregate function performs a calculation on a set of values and returns a single value.

MySQL provides many aggregate functions including AVG, COUNT, SUM, MIN , MAX, etc. An aggregate function ignores NULL values when it performs calculation except for the COUNT function.

AVG function

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.

AVG(expression)

You can use the AVG function to calculate the average buy price of all products in the products table by using the following query:

SELECT AVG(buyPrice) average_buy_price FROM products

The AVG function in detail.

COUNT function

The COUNT function returns the number of the rows in a table. For example, you can use the COUNT function to get the number of products in the products table as the following query:

SELECT COUNT(*) AS Total FROM products

The COUNT function has several forms such as COUNT(*) and COUNT(DISTINCT expression). For more information, check it out the COUNT function tutorial.

SUM function

The SUM function returns the sum of a set of values. The SUM function ignores NULL values. If no matching row found, the SUM function return NULL.

To get the total sales of each product, you can use the SUM function in conjunction with the GROUP BY clause as follows:

SELECT productCode,sum(priceEach * quantityOrdered) total FROM orderdetails GROUP by productCode

To see the result in more detail, you can join the orderdetails table to the products table as the following query:

SELECT P.productCode,        P.productName,        SUM(priceEach * quantityOrdered) total FROM orderdetails O INNER JOIN products  P ON O.productCode = P.productCode GROUP by productCode ORDER BY total

More information on the SUM function in detail.

MAX function

The MAX function returns the maximum value in a set of values.

原文链接：http://outofmemory.cn/mysql/function/mysql-aggregate-functions