如何在SQL中获取分组统计列的平均值？

你遇到的Error Code: 1111错误，原因是不能直接在聚合函数avg()中嵌套另一个聚合函数count()。GROUP BY user_id已经将数据按用户分组并计算了每组的照片数量，此时外层的avg()无法直接对分组后的聚合结果进行计算。

要获取分组统计列的平均值，你需要先将分组统计的结果作为一个临时数据集，再对这个数据集的统计值求平均，以下是两种可行的写法：

方法1：使用子查询 #

SELECT AVG(user_photo_count)
FROM (
    -- 先按user_id分组统计每个用户的照片数量
    SELECT COUNT(user_id) AS user_photo_count
    FROM photos
    GROUP BY user_id
) AS user_counts;

方法2：使用CTE（公共表表达式，适用于MySQL 8.0+、PostgreSQL等支持的数据库） #

WITH user_counts AS (
    -- 定义临时数据集，存储每个用户的照片数量
    SELECT COUNT(user_id) AS user_photo_count
    FROM photos
    GROUP BY user_id
)
-- 对临时数据集的统计值求平均
SELECT AVG(user_photo_count) FROM user_counts;

这两种写法都会先得到你第一条查询输出的所有数值，再计算这些数值的平均值，完美避开聚合函数嵌套的问题。

内容的提问来源于stack exchange，提问作者user19365190