AVG() 回傳數值欄位的平均值,忽略 NULL 值。
SELECT AVG(column_name) FROM table_name;
+----+------------+------------+----------+
| Id | OrderDate | OrderPrice | Customer |
+----+------------+------------+----------+
| 1 | 2008/11/12 | 1000 | Hansen |
| 2 | 2008/10/23 | 1600 | Nilsen |
| 3 | 2008/09/02 | 700 | Hansen |
| 4 | 2008/09/03 | 300 | Hansen |
| 5 | 2008/08/30 | 2000 | Jensen |
| 6 | 2008/10/04 | 100 | Nilsen |
+----+------------+------------+----------+
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders;
+--------------+
| OrderAverage |
+--------------+
| 950 |
+--------------+
計算:(1000+1600+700+300+2000+100) / 6 = 5700 / 6 = 950
SELECT Customer, AVG(OrderPrice) AS AvgOrder
FROM Orders
GROUP BY Customer;
+----------+----------+
| Customer | AvgOrder |
+----------+----------+
| Hansen | 666.67 |
| Nilsen | 850 |
| Jensen | 2000 |
+----------+----------+
NULL 值不參與計算 (不計入分母)。CAST 可控制回傳精度:CAST(AVG(price) AS DECIMAL(10,2))。