Average a column returned from a subquery

Question

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`
);

Answer 1

You need to create a subquery if you want to do this. By creating the subquery you then give the SUM() column a name and then AVG() that new field.

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

Answer 2

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')

(This assumes lead_id is never NULL.)

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

Answer 3

Sure, just give the result column a name and select from it:

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