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 值將被忽略。
