每日一结：典型错误之“ERROR 1111 (HY000): Invalid use of group function”

列如：SQL22 统计每个学校的答过题的用户的平均答题数

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

代码如下：

select u.university,avg(count(q.result)),q.device_id from user_profile u join question_practice_detail q on q.device_id = u.device_id  group by university ;

-- 首先不能在聚合函数（例如 AVG）内部使用 COUNT 函数，因为这不是有效的语法。

-- 其次，还需要根据 "university" 列进行分组，但在选择列表中却没有包括这个列。

错误原因：

错误的原因在于代码中混合了聚合函数（AVG 和 COUNT）的使用，而这两个函数在一个单独的查询中不应该同时使用。下面进行详细解释以及补充相关知识点：

1. 聚合函数的使用：

   • AVG 函数用于计算一列数字的平均值。
   • COUNT 函数用于计算一列中非空值的数量。

2. 混合使用的问题： 在我的查询中，我试图计算每个大学的平均问题作答次数。然而，我在 AVG 函数内部使用了 COUNT 函数，这是不正确的。AVG 函数需要一个数字列作为输入，然而 COUNT 函数返回的是计数值，并不是一个单一的数字。这会导致语法错误。

3. 正确的做法： 正确的做法是首先计算每个设备的问题作答次数，然后再计算每个大学的平均问题作答次数。这就需要两个不同的查询步骤。首先使用 COUNT 函数来计算每个设备的问题作答次数，然后使用 AVG 函数计算每个大学的平均问题作答次数。

4. 关于分组： 当使用聚合函数时，通常需要使用 GROUP BY 子句来指定如何对数据进行分组。在我的查询中，我试图根据大学进行分组，这是正确的，因为我想要计算每个大学的平均问题作答次数。

正确代码如下：

select u.university, avg(q.answer_count) as avg_answer_count
from user_profile u
join (
    select device_id, count(*) as answer_count
    from question_practice_detail
    group by device_id
) q on q.device_id = u.device_id
group by u.university;


--SELECT u.university, AVG(q.answer_count) AS avg_answer_count

--这一部分是查询语句的开始，它表示我们要从数据库中选择两个字段进行展示：
--u.university: 这是用户的大学名称。

--AVG(q.answer_count) AS avg_answer_count: 这是一个平均值的计算。我们从子查询中获取每个设备的问题作答次数，然后计算这些次数的平均值，并将结果命名为 avg_answer_count。

--FROM user_profile u

--这部分指定了我们要从名为 user_profile 的表中获取数据，并将其表示为 u，我们在查询中可以使用这个表示。

--JOIN (SELECT device_id, COUNT(*) AS answer_count FROM question_practice_detail GROUP BY device_id) q ON q.device_id = u.device_id

--这部分是一个连接操作，它将用户数据与问题练习详细信息进行关联。让我分解它：

--(SELECT device_id, COUNT(*) AS answer_count FROM question_practice_detail GROUP BY device_id) q: 这是一个子查询，用于计算每个设备的问题作答次数。我们从 question_practice_detail 表中选择 device_id 列，并通过 COUNT(*) 函数计算出每个设备的问题作答次数。使用 GROUP BY 子句按照设备ID分组，以便计算每个设备的问题作答次数。

--ON q.device_id = u.device_id: 这部分表示连接条件。我们将子查询的结果与 user_profile 表中的数据连接，使用设备ID (device_id) 作为连接条件。

--GROUP BY u.university这是一个分组操作，它将查询结果按照大学名称进行分组。这样，我们可以在每个大学内计算平均问题作答次数。

解题思路：

1. 选择要显示的字段和计算平均值： 首先，我们选择要显示的字段，其中包括用户的大学名称和平均问题作答次数。平均问题作答次数是通过将问题作答次数相加并除以总设备数得出的。

2. 连接用户信息和问题练习详细信息： 我们从两个表中获取数据：user_profile 表和 question_practice_detail 表。这些表在查询中通过 JOIN 操作连接起来。我们使用用户设备ID作为连接的关键。这样，我们可以将每个用户的大学信息与其相关的问题练习详细信息关联起来。

3. 子查询计算问题作答次数： 在连接的过程中，我们执行了一个子查询。这个子查询用于计算每个设备的问题作答次数。我们从 question_practice_detail 表中选择了设备ID列，并使用 COUNT(*) 函数来计算每个设备的问题作答次数。通过使用 GROUP BY 子句，我们确保按设备ID分组，以便在连接时可以将这些计数与用户的大学信息对应起来。

4. 分组操作： 最后，我们使用 GROUP BY 子句将结果按照大学名称进行分组。这样，我们可以在每个大学内计算平均问题作答次数。分组后，我们可以在结果中看到每个大学以及其对应的平均问题作答次数。

5. 得出结果： 最终查询的结果将包括每个大学的名称以及平均问题作答次数。这个结果将帮助你了解每个大学的学生在问题作答方面的表现。