SQL AVG() 函数

AVG() 函数

AVG() 函数返回数值列的平均值。

SQL AVG() 语法

SELECT AVG(column_name) FROM table_name

演示数据库

在猿变手册教程中，我们将使用 yuanbian demo数据库。

下面是选自 "orders" 表的数据：

+----------+--------------+----------+--------------+----------------+---------------------+
| order_id | order_no     | goods_id | order_amount | order_quantity | create_date         |
+----------+--------------+----------+--------------+----------------+---------------------+
|        1 | 201503120001 |       10 |         1000 |             10 | 2015-03-12 09:00:04 |
|        2 | 201503120002 |       12 |         1300 |             12 | 2015-03-12 17:23:10 |
|        3 | 201503130001 |       11 |         2400 |             22 | 2015-03-13 10:30:12 |
|        4 | 201503130002 |       12 |         2500 |             15 | 2015-03-13 11:00:05 |
|        5 | 201503130003 |       10 |         1400 |             15 | 2015-03-13 14:54:00 |
|        6 | 201503140001 |       12 |         2100 |             12 | 2015-03-14 11:34:00 |
+----------+--------------+----------+--------------+----------------+---------------------+

AVG() 示例

下面的 SQL 语句从 "orders" 表的 "order_amount" 列获取平均值：

SELECT AVG(order_amount) AS order_avg_amount
FROM orders;

执行以上 SQL 输出结果如下：

+--------------------+
| order_avg_amount   |
+--------------------+
| 1783.3333333333333 |
+--------------------+

在where子句中使用AVG()结果

下面的 SQL 语句选择订单金额高于平均订单金额的订单：

SELECT * FROM orders
WHERE order_amount > (SELECT AVG(order_amount) FROM orders);

执行以上 SQL 输出结果如下：

+----------+--------------+----------+--------------+----------------+---------------------+
| order_id | order_no     | goods_id | order_amount | order_quantity | create_date         |
+----------+--------------+----------+--------------+----------------+---------------------+
|        3 | 201503130001 |       11 |         2400 |             22 | 2015-03-13 10:30:12 |
|        4 | 201503130002 |       12 |         2500 |             15 | 2015-03-13 11:00:05 |
|        6 | 201503140001 |       12 |         2100 |             12 | 2015-03-14 11:34:00 |
+----------+--------------+----------+--------------+----------------+---------------------+