平均从子查询返回的列 [英] Average a column returned from a subquery

问题描述

不能对子查询的结果求平均吗?

Is is not possible to average the results from a subquery?

这是我正在处理的查询:

Here's the query I'm fighting with:

SELECT AVG(
    SELECT SUM(`retail_subtotal`)
      FROM `order`
     WHERE `status` IN('O')
  GROUP BY `lead_id`
);

推荐答案

实际上，一种更简单的查询方式是没有子查询:

Actually, an easier way to phrase the query is without a subquery:

SELECT SUM(`retail_subtotal`)/count(distinct lead_id) as avg
FROM `order`
WHERE `status` IN ('O')

(假设lead_id 永远不会为NULL.)

(This assumes lead_id is never NULL.)

您的原始查询有问题不仅是因为 avg() 中的子查询，还因为子查询返回了多行.

Your original query had a problem not only because of the subquery in the avg(), but also because the subquery returned multiple rows.

这篇关于平均从子查询返回的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！