sql聚合函数_SQL聚合函数

sql聚合函数

SQL Aggregate Functions basically operate on multiple columns to perform the operations and serve to represent the output as an entity representing the operation executed.

SQL聚合函数基本上在多个列上进行操作以执行操作，并将输出表示为表示所执行操作的实体。

SQL Aggregate Functions SQL聚合函数

Syntax:

句法：

aggregate_function (DISTINCT | ALL expression)

• DISTINCT enables the user to select distinct values from the table i.e. if multiple attributes contain the same value, then only single distinct value is considered for the calculations.
  DISTINCT使用户可以从表中选择不同的值，即，如果多个属性包含相同的值，则仅考虑单个不同的值进行计算。
• ALL makes sure that even the repeated values are considered for the calculations.
  ALL确保即使重复值也被考虑用于计算。

---

---

SQL中的聚合函数： (Aggregate Functions in SQL:)

• AVG()
  AVG（）
• MIN()
  MIN（）
• MAX()
  MAX（）
• SUM()
  和（）
• COUNT()
  计数（）

---

---

1. SQL AVG（）函数 (1. SQL AVG() function)

SQL AVG() function returns the average of all the selected values from the corresponding column of the table.

SQL AVG（）函数从表的相应列返回所有选定值的平均值 。

Let’s us consider the following table to understand the Aggregate functions:

让我们考虑下表以了解Aggregate函数：

Table Name: Details

表名称： 详细信息

ID	Name	Amount	Age
1	Safa	5000	21
2	Aman	2500	23
3	Rehaan	20000	25
4	Seema	12000	25

ID	名称	量	年龄
1个	萨法	5000	21
2	阿曼	2500	23
3	瑞安	20000	25
4	西玛	12000	25

Example:

例：

select AVG(Amount) from Details;

Output:

输出：

9875

---

---

2. SQL MIN（）函数 (2. SQL MIN() function)

SQL MIN() function returns the minimum value of all the selected values from the corresponding column of the table.

SQL MIN（）函数从表的相应列返回所有选定值的最小值 。

Example:

例：

select MIN(Amount) from Details;

Output:

输出：

2500

---

---

3. SQL MAX（）函数 (3. SQL MAX() function)

SQL MAX() function returns the maximum value of all the selected values from the corresponding column of the table.

SQL MAX（）函数从表的相应列返回所有选定值的最大值 。

select MAX(Amount) from Details;

Output:

输出：

20000

---

---

4. SQL SUM（）函数 (4. SQL SUM() function)

SQL SUM() function returns the summation of all the selected values from the corresponding column of the table.

SQL SUM（）函数从表的相应列返回所有选定值的总和 。

示例1： SUM（）函数的基本理解 (Example 1: Basic Understanding of SUM() Function)

select SUM(Amount) from Details;

Output:

输出：

39500

示例2：带有GROUP BY子句SQL SUM（） (Example 2: SQL SUM() with GROUP BY Clause)

SQL SUM() can be framed together with SQL GROUP BY Clause to represent the output results by a particular label/values.

SQL SUM（）可以与SQL GROUP BY子句一起使用，以特定的标签/值表示输出结果。

SELECT SUM(Amount),Name  
FROM Details 
WHERE Age>21 
GROUP BY Name;  

Output:

输出：

SUM() with GROUP BY SUM（）与GROUP BY

示例3：具有HAVING子句SQL SUM（） (Example 3: SQL SUM() with HAVING Clause)

SQL SUM() function can be used along with SQL HAVING Clause; HAVING Clause is basically used to specify the condition to be operated on the set of values in the table.

SQL SUM（）函数可以与SQL HAVING子句一起使用。 HAVING子句基本上用于指定要对表中的一组值进行操作的条件。

SELECT SUM(Amount),Name  
FROM Details 
GROUP BY Name
HAVING SUM(Amount)>2500;

Output:

输出：

SUM() with HAVING Clause 具有HAVING子句的SUM（）

---

---

5. SQL COUNT（）函数 (5. SQL COUNT() function)

示例 1： COUNT（）函数返回表中特定列的计数 (Example 1: COUNT() function to return the count of a particular column of a table)

select COUNT(Amount) from Details;

Output:

输出：

4

示例2：COUNT（*）函数 (Example 2: COUNT(*) function)

This function returns the count of all the values present in the set of records of a table.

此函数返回表的记录集中存在的所有值的计数。

SELECT count(*) from Details;

Output:

输出：

4

示例3：带有WHERE子句的COUNT（） (Example 3: COUNT() with WHERE Clause)

SELECT count(*) from Details
where Age<25;

‘Output:

输出：

2

示例4：COUNT（）和GROUP BY子句 (Example 4: COUNT() with GROUP BY Clause)

SELECT count(Amount),Name from Details
where Age<25
Group by Name;

Output:

输出：

COUNT() with GROUP BY Clause COUNT（）和GROUP BY子句

示例5：带有HAVING子句的COUNT（） (Example 5: COUNT() with HAVING Clause)

SELECT count(Amount),Age, Name from Details
Group by Name
HAVING Age>=25;

Output:

输出：

COUNT() with HAVING Clause COUNT（）和HAVING子句

---

---

结论 (Conclusion)

Thus, in this article, we have understood different SQL Aggregate Functions.

因此，在本文中，我们了解了不同SQL聚合函数。

---

---

参考 (Reference)

• SQL Documentation
  SQL文档

翻译自: https://www.journaldev.com/34288/sql-aggregate-functions

sql聚合函数