一、五个函数各自的活

sum 求和、avg 求平均、max 最大值、min 最小值、count 计数。 前四个的参数是一列，返回一个数。 它们的作用范围默认是「整张表」：不加 group by 时，整表算一组，所以只出一行。 下一节讲怎么按组算。

这张表里最该记住的是最后一列：只有 count(*) 不把 null 当回事， 其余全跳过。 跳过就意味着「分母变小」，avg 最容易在这上面出错。

二、avg 的分母：值得单独算一遍

avg(x) 的算法就是 sum(x) / count(x)。 分母是「x 非空的行数」，不是「总行数」。 Demo 里 sum(height) / count(*) 给 168.44，这是真正的平均值。 把分母换成 count(phone) 就变 183.75——因为孙悦没填手机号，分母从 12 掉到 11。 这个错最难查：两个数都「看着正常」， 没有报错、没有 NULL，只是整体偏高。 数据里只要有一列有空值，就得回头看 avg 的分母。

三、max 和 min 不只对数字管用

字符串也能比：max(name) 给「黄磊」、min(name) 给「刘洋」，按编码序比，不是拼音序。 日期也能比：max(birthday) 给 2007-10-02，min 给 2006-11-30。 只要格式是补零的 年-月-日，字面序就等于时间序。 所以日期别存成 2026-4-5。 不补零的话，2026-10-01 会排在 2026-4-5 前面， 比出来的大小全是错的。

四、坑一：空集时 sum 给 null，count 给 0

条件筛不到任何行时，sum、avg、max、min 一律给 NULL，只有 count 给 0。 后果是「null 传染」：sum 的结果拿去做加法，整条式子还是 null； 拿去显示，报表上就是一个空单元格，看着像「没数据」而不是「零」。 办法是 coalesce(值, 0)：值为 null 就换成 0。 写报表时几乎是标配，别等出了事故才想起来。

五、坑二、坑三：不能嵌套，也不能混着普通列选

max(count(*)) 报错——聚合是一次算完的，里面不能再套一层。 「最大的组有多少行」要先分组数出来，再套一层子查询。 select name, max(height) 这里给了一行，看着像「最高的那个人」。 其实引擎只是随便挑了某一行，把它的 name 和全表的 max 并在一起。 MySQL 8 默认开着 ONLY_FULL_GROUP_BY，同一句直接报错。 这条限制是在救你： 它逼你把「哪一行」说清楚。 要真有那个人，用 ORDER BY height DESC LIMIT 1。

六、动手 Demo：五个函数，三种 count，三个坑

Demo 七段：先一次算四个数，再掰开 count 的三种写法，然后单独算一遍 avg 的分母。 后半段是三个坑：空集、嵌套、混列。前两个能跑出结果，第三个能跑出「像样但错」的结果。

import _q46 as Q
c = Q.con()

# 一次算四个数
c.execute("SELECT SUM(height) AS s, AVG(height) AS a, "
          "MAX(height) AS mx, MIN(height) AS mn FROM students")
# count 的三种写法
c.execute("SELECT COUNT(*) AS rows_all, COUNT(phone) AS has_phone, "
          "COUNT(DISTINCT class_id) AS class_kinds FROM students")
# avg 的分母是「非空行数」
c.execute("SELECT SUM(height) / COUNT(*) AS right_way, "
          "SUM(height) / COUNT(phone) AS wrong_way FROM students")
# max/min 对字符串、日期也能用
c.execute("SELECT MAX(name) AS mx_name, MIN(name) AS mn_name, "
          "MAX(birthday) AS mx_bd, MIN(birthday) AS mn_bd FROM students")
# 空集：sum 给 NULL、count 给 0
c.execute("SELECT SUM(amount) AS s, AVG(amount) AS a, COUNT(*) AS n "
          "FROM orders WHERE status = '不存在'")
c.execute("SELECT COALESCE(SUM(amount), 0) AS safe_sum FROM orders "
          "WHERE status = '不存在'")
# 聚合不能嵌套：报错
c.execute("SELECT MAX(COUNT(*)) FROM students")
# 混着普通列：这边给一行，MySQL 8 报错
c.execute("SELECT name, MAX(height) AS tallest FROM students")

实跑输出：

--------------------------------------------------------------------
第十六课：五个聚合函数，和它们共同的三个坑
--------------------------------------------------------------------

  sum、avg、max、min：一次算出四个数
  SQL> SELECT SUM(height) AS s, AVG(height) AS a, MAX(height) AS mx, MIN(height) AS mn
       FROM students
  s           a          mx         mn
  -----------------------------------------
  2021.30     168.44     180.20     158.60
  （1 行）

  **`sum` 求和、`avg` 求平均、`max` 最大值、`min` 最小值。**
  **它们都把「一堆行」压成「一个值」，所以叫聚合函数。**

  count：三种写法，数的是三样东西
  SQL> SELECT COUNT(*) AS rows_all, COUNT(phone) AS has_phone,
       COUNT(DISTINCT class_id) AS class_kinds FROM students
  rows_all     has_phone     class_kinds
  ---------------------------------------
  12           11            3
  （1 行）

  **`count(*)` 数行数，给 12。**
  **`count(phone)` 数「这一列非空的有几个」，给 11**——孙悦没填手机号，那一行被跳过。
  **`count(distinct class_id)` 数「有几种不同的值」，给 3。**
  **`count(*)` 是唯一把 null 行也算进去的**，另外两个都跳过 null。

  avg 的分母：这个坑值得单独算一遍
  SQL> SELECT SUM(height) / COUNT(*) AS right_way, SUM(height) / COUNT(phone) AS wrong_way
       FROM students
  right_way     wrong_way
  ------------------------
  168.44        183.75
  （1 行）

  **`avg(height)` 的算法就是 `sum(height) / count(height)`。**
  **分母是「非空的行数」，不是「总行数」。**
  **右边那列把分母换成了 `count(phone)`（11 行），结果从 168.44 变成 183.75。**
  **数据里只要有一列有空值，用错分母就会算出一个看着正常、其实全错的平均值。**

  max 和 min 不只对数字：字符串、日期也能比
  SQL> SELECT MAX(name) AS mx_name, MIN(name) AS mn_name, MAX(birthday) AS mx_bd,
       MIN(birthday) AS mn_bd FROM students
  mx_name     mn_name     mx_bd          mn_bd
  --------------------------------------------------
  黄磊        刘洋        2007-10-02     2006-11-30
  （1 行）

  **字符串按编码序比**，所以最大的是「黄磊」，不是拼音最后一个。
  **日期按字面比**——只要格式是 `年-月-日` 且月份日期补零，字面序就等于时间序。
  **这也是为什么日期别存成 `2026-4-5` 这种不补零的样子**，**不补零，字面序就全乱了。**

  坑一：空集时 sum 给 null，count 给 0
  SQL> SELECT SUM(amount) AS s, AVG(amount) AS a, COUNT(*) AS n FROM orders
       WHERE status = '不存在'
  s        a        n
  --------------------
  NULL     NULL     0
  （1 行）

  **一行都没有的时候，`sum` 和 `avg` 给 `NULL`，`count(*)` 给 `0`。**
  **程序里直接拿 `sum` 的结果去做加法，就会碰上「null 加任何数还是 null」。**

  SQL> SELECT COALESCE(SUM(amount), 0) AS safe_sum FROM orders WHERE status = '不存在'
  safe_sum
  ---------
  0
  （1 行）

  **`coalesce(值, 0)` 是「值为 null 就换成 0」，写报表时几乎必备。**

  坑二：聚合函数不能嵌套
  SQL> SELECT MAX(COUNT(*)) FROM students
  报错：misuse of aggregate function COUNT()

  **「最大的那个组有多少行」听着合理，但不能这么写**——聚合是一次算完的，不能在里面再套一层聚合。
  **办法是先分组数出来，再套一层子查询或排序取第一条。**

  坑三：混着普通列选，这边给一行，MySQL 8 直接报错
  SQL> SELECT name, MAX(height) AS tallest FROM students
  name     tallest
  -----------------
  黄磊     180.20
  （1 行）

  **这里给了一行「黄磊 180.20」，看着像「最高的那个人」。**
  **其实引擎只是随便挑了某一行，把它的 `name` 和全表的 `MAX(height)` 并在一起。**
  **MySQL 8 默认开着 `ONLY_FULL_GROUP_BY`，同一句会直接报错**，
  信息里说「聚合查询里有列不在 `GROUP BY` 里」。
  **想真要那个人，得用子查询，或者 `ORDER BY height DESC LIMIT 1`。**

第一段四个数。 sum 2021.30、avg 168.44、max 180.20、min 158.60。 第二段三种 count。 12、11、3——行数、非空数、种类数，三个完全不同的东西。 第三段分母。 左边 168.44 是对的，右边 183.75 是分母用错的版本。 第四段字符串与日期。 最大最小照样能比，但要记住是按编码序。 第五段空集。 两个 NULL 加一个 0；coalesce 那句把 NULL 换成了 0。 第六段嵌套。 直接报 misuse of aggregate function。 第七段混列。 给了一行「黄磊 180.20」，这一行不是查询的答案，是巧合。

30 秒实验

1. COUNT(*) 与 COUNT(phone) 差的那 1 行，是哪个人？用 where 找出来。
2. 把 avg(height) 换成 sum(height) / count(*)，两个数一样吗？
3. SELECT MAX(birthday) 给的是最晚出生的还是最早出生的？

常见误区

1. 以为 count(列) 和 count(*) 一样。 只要那一列有 null，两边的数就对不上， 而这个差往往是「数据质量问题」的第一条线索。
2. 用 avg 时不看分母。 分母是「非空行数」，有空值时算出来的平均值会悄悄偏高或偏低。
3. 把 select name, max(height) 当成「取最高的那个人」。 它给的是「随便一行的 name + 全表最大值」，MySQL 8 会直接拒绝这种写法。

聚合函数默认把整表算成一行。下一篇按某个标准把行归堆，一堆出一行——group by。

• ☐ （本篇完，下一篇 4.3 分组子句：group by）

相关笔记

• 算术运算
• 分组子句：group by
• 🦏 MySQL即学即用 · 索引