SQL COUNT(), AVG() 和 SUM() 函数
SQL COUNT(), AVG() 和 SUM() 函数
The COUNT() function returns the number of rows that matches a specified criterion. (COUNT() 函数返回匹配指定条件的行数。)
COUNT() 语法
SELECT COUNT(column_name)
FROM table_name
WHERE condition;The AVG() function returns the average value of a numeric column. (AVG() 函数返回数值列的平均值。)
AVG() 语法
SELECT AVG(column_name)
FROM table_name
WHERE condition;The SUM() function returns the total sum of a numeric column. (SUM() 函数返回数值列的总和。)
SUM() 语法
SELECT SUM(column_name)
FROM table_name
WHERE condition;演示数据库
Below is a selection from the "Products" table in the Northwind sample database (以下是 Northwind 示例数据库中的 "Products" 表的选择结果)
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 |
| 4 | Chef Anton's Cajun Seasoning | 2 | 2 | 48 - 6 oz jars | 22 |
| 5 | Chef Anton's Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
COUNT() 示例
The following SQL statement finds the number of products (以下 SQL 语句查找产品的数量)
注意： NULL 值不被计算。
AVG() 示例
The following SQL statement finds the average price of all products (以下 SQL 语句查找所有产品的平均价格)
注意： NULL 值将被忽略。
演示数据库
Below is a selection from the "OrderDetails" table in the Northwind sample database (以下是 Northwind 示例数据库中的 "OrderDetails" 表的选择结果)
| OrderDetailID | OrderID | ProductID | Quantity |
|---|---|---|---|
| 1 | 10248 | 11 | 12 |
| 2 | 10248 | 42 | 10 |
| 3 | 10248 | 72 | 5 |
| 4 | 10249 | 14 | 9 |
| 5 | 10249 | 51 | 40 |
SUM() 示例
The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table (以下 SQL 语句查找 "OrderDetails" 表中 "Quantity" 字段的总和)
注意： NULL 值将被忽略。
