萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 彙總函式:AVG() 平均值計算

AVG() 回傳數值欄位的平均值,忽略 NULL 值。

語法

SELECT AVG(column_name) FROM table_name;

範例資料:Orders 表

+----+------------+------------+----------+
| 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))。
copyright © 萬盛學電腦網 all rights reserved