MYSQL: Averaging the sum of two columns-CodePudding

Using MYSQL I am trying to get the avg amount spent by all the customers after determining the sum of what each customer spent.

select customernumber, round(sum(price_per_each*quantity_ordered),2) as 'ordertotal'
from orderdetails
join orders using (ordernumber)
join customers using (customernumber)
group by customernumber;

This gives me the sum of what each customer has spent across multiple orders. The results of this query are about hundred records, ranging from 8k to 900k.

I now need to get the avg of all the sum totals shown in the previous query. So far every time I try to write this, I get an error message regarding invalid use of group function.

When I try getting the average by using division via count(*), the number I get is in the 3k range which is too small compared to what is expected.

Please help. I am just starting to learn MySql and cannot seem to figure this out after several hours.

CodePudding user response：

I would try the AVG function over the ordertotal column.

SELECT AVG(`ordertotal`)
FROM (
   select customernumber, round(sum(price_per_each*quantity_ordered),2) as 'ordertotal'
   from orderdetails
   join orders using (ordernumber)
   join customers using (customernumber)
   group by customernumber
) nested;